To speed up retrieval and searching of the data in your tables it's a good idea to add Indexes to certain fields in your tables. An index is a data structure held within the database that effectively stores a pointer to a particular record instead of having to scan every record. It places a small overhead as the index needs to be maintained every time you add a new record but if your database grows in size then the advantage is obvious.
Access provides us with a way of indexing our fields in tables simply choosing Yes from the Index option in the Table designer with the field highlighted you want to index. We will probably want to search our database by the Artist Name so adding an index to this field makes sense.
There are three options in the dropdown:-
No
Yes (Duplicates OK)
Yes (No Duplicates)
You could assume that no two bands will ever have the same name but this isn't strictly so. As noted previously there have been two Nirvanas, if you chose the No Duplicates option and tried to enter Nirvana twice into your table you would receive an error. You will have to use your judgement as to whether to use a Unique index or Non Unique one. I'm going to choose No Duplicates.
What fields do you choose to index? There isn't actually a right or wrong choice but you should consider indexing any field you will be searching on. For each table create the following indexes:-
Album
AlbumTitle - Duplicates OK
Track
TrackTitle - Duplicates OK
Country
CountryName - No Duplicates
Genre
GenreDescription - No Duplicates
Remember that Access will automatically create indexes on your Primary Key fields and you cannot change this. It makes sense to have your unique identifier field indexed but it's something you need to know.
Relationships
Relational databases work by linking related tables of data together via foreign key relations. In our database we have a table that stores data on Artists and one that holds data on Albums. There is a link between those items in the real world and you can express it by saying Artist X has released many Albums called X,Y,Z. This is what is know as a One to Many relationship.
In Access we can define these relationships between our tables and this is where the power of Relational databases is. From the Create tab on the menu ribbon, click Relationships. A blank form will appear, right click anywhere on the form and click the Show Table item. Double click the Artist and Album table to add them to the form. We will be adding all of the tables soon but we can concentrate on these two first.
Click on the ArtistID field and while holding down the button drag a line across to the FKArtistID field in the Album table. Upon release a dialog box will open with various options.
Check the Enforce Referential Integrity, Cascade Update and Cascade Delete boxes.
Enforce Referential Integrity means that the value in this field can only come from values stored in the Artist table. Effectively it means that you can only store albums where there is a related artist present in your data.
If you go back to the data we entered we can see that we have two artists at present, Hawkwind and Ozric Tentacles. They have primary key values of 1 and 2 respectively. When we add an album to the Album table we have to enter a value into the FKArtistID field and at the moment we can only enter 1 or 2. In the case of Hawkwind with an ArtistID of 1, we can see that in the Album table In Search of Space has a value of 1 in the FKArtistID field. This means that album is related to that artist. Referential Integrity rules mean we can only have albums if there is a related artist.
The cascade updates and deletes options mean that if a primary key field (ArtistID) changes in the Artist table it will be cascade through to the FKArtistID field in the Album table thus keeping the link. Cascade deletes means that if we deleted the Hawkwind record from the Artist table all albums that have the same FKArtistID would be deleted too. This prevents us from having albums for which there are no artists.
The Relationship type of One to Many means that one Artist in the Artist table can have zero or many albums in the Album table. There are other relationship types such as One to One and Many to Many but I won't cover these here. Feel free to Google about database relation types for an in depth explanation.
Go ahead and add the other tables to the Relationship form. Use the following list to see which fields in each table you need to connect together. Ensure you tick the Enforce Referential Integrity boxes as well as the Cascade options.
1. Album to Track - Link AlbumID from Album table to FKAlbumID in Track table.
2. Genre to Album - Link GenreID fromGenre table to FKGenreID in Album table.
3. Country to Artist - Link CountryID from Country table to FKCountryID in Artist table.
The relationships have now been set up so we can move on to Queries and start exploring how to retrieve data for meaningful purposes.
No comments:
Post a Comment