Know what you want

The first thing you should do when you have a relational database, is to know what you want.

What happens in the database and what do you want to achieve?

In this example, I want to create a database of inventory in my warehouse.

The inventory consists of songs on CD or cassette.

These are all together and spread in different cabinets.

The purpose of my database is to easily find a song in my warehouse.

This applies both when I search by title, by singer, or by genre.

I can easily add songs.

Define your fields

Once you know what you want, you determine the various fields.

Each song has a title, you need a field with the “title”.

Each song has a singer, so you also need a field with the “singer”.

Each song has a genre, so you also need a field for “Genre”.

You should also know in which cabinet you can find the song so, a “location” field is useful.

Since a song can be on CD or cassette, a “type” field can also be used.

Possibly add in the duration of the song, the date when it was published and give each song a rating value.

The next thing you do is to place these fields in different tables.

Note that the “Singer” appears in “Album” and “Song” too.

This is because our album can contain several songs with different singers.

I can answer that when we define our “Primary keys” in the next section of this lesson.

Determine your “Primary keys”

Now that we have written all fields that we need in our database on paper, we simplify this by identifying which are the “Primary keys” and determine related fields.

In the “Album” table, we have a “Primary key” since, each album is indeed unique.
So here we add a field with a “Primary key”, eg “AlbumID”.
And because we want to connect to the “Songs” table, this field is also related key to this table.

The “Genre” field from the “Genre” table can be placed in the “Song” table since each song has one “genre”.

Same goes for the “Type” field from the “Type” table and the “Location” field from the “Location” table.
Each album may have only one type and can have only one location.
So we can move these two fields, to the “Album” table.

In the “Album” table and the “Songs” table, we have the same field namely, “Group”.
How do we solve this?
As an album may consist of more than one group, this field is variable.
Because a song can have only one lead singer, which is not variable, we create a “Lead Singers” table with “SingerID” as primary field.
And because we have the “Lead Singers” table to connect the “Songs” table, we add this field to the “Songs” table as related key.

What we have now is the unique field “AlbumID” that may occur only once in the “Album” table but, several times in the “Songs ” table.

We have the unique field ” SingerID” which can occur only once in the “Lead Singers” table, but several times in the “Songs” table.

In short, we have two one-to-many “Relationships”.

It seems that this format is correct, so we can start creating our database.
If it later proves that we need more changes, we can always change at any time in our database.

Create a new database

Before we make our tables, we must first create a database.

Open ” Access”.

This opens the “Getting Started with Microsoft Office Access”.
Click the “Blank Database” button.
Click the “Browse” button to navigate to a folder on your computer where you want to save the database.

Give the file a name and click the “Create” button.

This opens the database in “Datasheet View” with a table and a field in this table, namely the ID field.

In “Access”, we have a number of display options to edit the structure of our table.
“Datasheet View”, as you see in the picture above is one of them.
By clicking on the “View” button at the top, in the “Ribbon”, we can navigate between the different display options.

In the next lesson we will create all the tables with their fields.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats