Tables

Tables are where databases begin and it's vital to get table design correct before you load them with data. If you don't get it right at this stage you'll find subsequent query, form and report design very difficult and the database could be rendered useless.

The key to database table design is a process called Normalisation. Normalisation is how to design your tables to minimise redundant data and to break down large tables into smaller ones that are linked via relationships. Normalisation is very formal and is based on mathematical constructs so I'm not going be delving in too deeply. Experience in designing databases helps and if you make enough you instinctively know how to design tables correctly.

As our database we'll be creating in this tutorial is for recording information about music artists and albums, consider the following data held in an Excel table. It will help to explain what normalisation should achieve.

Artist	Formed	Country	Album	Released	Track Number	Title
Rush	1974	Canada	Fly By Night	1975	1	Anthem
Rush	1974	Canada	Fly By Night	1975	2	Best I Can
Rush	1974	Canada	Fly By Night	1975	3	Beneath, Between & Behind
Ozric Tentacles	1983	Enland	Pungent Effulgent	1989	1	Dissolution
Ozric Tentacles	1984	Enland	Pungent Efffulgent	1989	2	O-I

The first three rows all contain data related to Rush, showing an album title and three tracks on that album. Here we can see that there is repeating groups of data in 'Rush', 'Canada' and 'Fly By Night'. To store these records in a database we want to store data only once, not three times in the case of the band name 'Rush'. If you're a Prog Rock fan then you'll know Rush's back catalogue is a lot more extensive so this Excel spreadsheet would realistically have many more repeating groups. We want to store data relating to the artist in it's own table so 'Rush' only appears once; the same applies for Ozric Tentacles too. We can extract from the spreadsheet that a table holding data about an Artist would include:-

Artist
Rush
Ozric Tentacles

Looking at the information relating to the album 'Fly By Night', we can see again that the data repeats for every track we have on that album. We can say from the spreadsheet we need a table that holds data on Album releases:-

Album
Fly By Night
Pungent Effulgent

As with the artist, we only want to store the name of the album once.

Eagle eyed readers may have spotted a few inconsistencies in the spreadsheet. One error is that the country of origin for Ozric Tentacles has been misspelt and reads Enland. If you wanted to search your database for all artists from England then a query looking for 'England' would not return any rows for Ozric's. For this reason we can see that it would be better to create a table that holds information on Country and store 'England' only once, thus eliminating the chance of entering misspelt data. As a further example, you can see that Efffulgent has also been misspelt so a search of that album name would not return O-I in your results.

Country
England
Canada

The last pieces of data we can see in our spreadsheet is the track number and track title.  We can extract this data and store it in it's own table called track and link it using a relationship to the album.

Track
Track Number
Track Title

In addition to the four types of data shown above, I'm going to add in an extra piece of information that we can store. I'm also going to record an album's Genre to record the style of music an album belongs to.

Genre
Genre Description


Primary Keys

Each of our five data entities (Artist, Album, Track, Country, Genre) have to be uniquely identified to allow us to create relationships between the tables. Each row in each table is a unique record so we will define a primary key that identifies that row of data and no other. In the case of the Artist table, we could use the Artist Name as the unique identifier but it is possible, however improbable, that two or more artists could have the same name. Nirvana springs to mind as not only where they Kings of Grunge but there was also a similar named band in the 60's

The best way to uniquely identify a record in any Access table is to use an Autonumber. This will increment by one every time a new record is added therefore keeping every row unique. We can then use this Autonumber ID to link to our other tables using Foreign Keys.

If this sounds confusing, then it's a good time to begin designing the tables in Access, creating the fields that hold data and then defining the relationships between those tables. Go ahead and fire up your MusicDB in Access.

Customising Access Interface

One tip if you use Access on a regular basis is to take a little time in customising the interface for quick access to table design, query type, new forms, etc. Underneath the main ribbon  you'll see a narrow strip of customisable short cuts

Adding your own icons here really makes a difference when you are creating your database. Go ahead and click the downward pointing arrow at the end of the strip to bring up the following window.

Change the Choose commands from dropdown to All Commands. You can add any shortcuts you like but I like to add:-

Table
Query Design
Query Type:Append
Query Type:Crosstab
Query Type:Delete
Query Type:Make Table
Query Type:Select
Query Type:Update
Form Design
Report Design
Visual Basic
Align Bottom
Align Left
Align Right
Align Top
Size to Narrowest
Size to Shortest
Size To Tallest
Size To Widest

We'll probably add more as we go but they are enough to be going on with.

Create Your First Table

Click the Table icon on the quick menu to create a new blank table. Click View to go into Design view. You will be prompted to give the table a name. As we are creating a table to hold information on recording artists call the table Artist (no surprises on the name!)

The ID field is already filled in for you but you should rename it to ArtistID. It's essential to name any objects and fields correctly so you know exactly what it is by just reading the name. It makes life a lot easier when you come to Visual Basic. If you don't name them correctly you'll be forever jumping from screen to screen to find out what your object or field is for.

The ArtistID should already have a datatype of Autonumber. This means the field is an integer that will increment by one each time a record is added to the table. The little key symbol to the left denotes this is the Primary Key; the unique identifier for all records in the Artist table.

Create the following fields as shown below. Ensure you have the correct datatype too as this is important.

Field Name - Datatype - Field Size
ArtistName - Text - 50
YearFormed - Number Long Integer
FKCountryOriginID - Number Long Integer

That's it. There really isn't a lot to creating these tables at this stage. The hard work is in the planning and design of the tables before you get to this point.

Creating the remaining tables

You can now go ahead and create the remaining three tables in the database. The tables should be named Album, Track and Country and contain the following fields. Don't forget to choose the correct data type for each field and that the auto number ID fields have the key symbol to the left denoting this is the primary key.

Album
AlbumID - Auto number
AlbumTitle - Text - 100
YearReleased  - Number Long Integer
FKArtistID - Number Long Integer

Track
TrackID - Auto number
TrackNum - Number Long Integer
TrackTitle - Text - 100
TrackTime - DateTime  - Short Time
FKAlbumID - Number Long Integer

Country
CountryID - Autonumber
CountryName - Text - 50

Genre
GenreID - Autonumber
GenreDescription - Text - 50

Now that our tables are created, we'll need to populate them with some initial data before we move on to the Indexes and Relationships page. Eventually we will be designing some forms through which we will enter data but for now you can type it directly into the tables themselves.

We'll start by entering a list of Countries into the Country table. Double click the Country table in the Object explorer pane on the left to open the table for data entry. Enter the following country names into the CountryName field. You will not need to enter anything into the Autonumber field as this will increment by one each time you enter a country and hit enter.

Country

CountryID	CountryName
1	United Kingdom
2	USA
3	Canada
4	Ireland
5	France
6	Germany
7	Italy
8	Australia

Do the same procedure for all the tables and enter the data as shown below. The fields prefixed with 'FK' will become clear when we get to Relationships.

Artist

ArtistID	ArtistName	YearFormed	FKCountryOrigin
1	Hawkwind	1969	1
2	Ozric Tentacles	1983	1
3	Gong	1969	5

Album

AlbumID	AlbumTitle	YearReleased	CatalogNum	FKGenreID	FKArtistID
1	In Search of Space	1971		2	1
2	Pungent Effulgent	1989		2	2

Genre

GenreID	GenreDescription
1	Rock
2	Progressive Rock
3	Folk
4	Folk Rock
5	Ambient

Track

TrackID	TrackNumber	TrackTitle	TrackLength	FKAlbumID
1	1	You Shouldn't Do That	15:42	1
2	2	You Know You're Only Dreaming	06:38	1
3	3	Master of the Universe	06:17	1
4	4	We Took The Wrong Step Years Ago	04:50	1
5	5	Adjust Me	05:45	1
6	6	Children of the Sun	03:21	1
7	1	Dissolution	06:15	2
8	2	O-I	03:58	2
9	3	Phalarn Dawn	07:35	2
10	4	The Domes of G'bal	04:35	2
11	5	Shaping the Pelm	06:10	2
12	6	Ayurvedic	10:54	2
13	7	Kick Muck	03:54	2
14	8	Agog in the Ether	04:05	2
15	9	Wreltch	08:32	2
16	10	Ayurvedism	19:04	2