 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
Creating an Access Database - Step 1: Database Design
Before you even get as far as switching on the computer and using Access in anger, you should sit down and design the database. A properly designed database will save you a lot of hassle in the long run. We can split the database design process down into various stages:
- System requirements (i.e.. what the system is designed to do)
- Object Analysis (i.e.. what data is going to be stored in the system)
- Object Relationship (How the various objects are related to each other)
- To list all CDs and records that I own, including track details;
- To give me a total cost, for insurance purposes;
- To find out which albums a certain song appears on;
- Because I sometimes use the CD player on the computer, I need to be able to get the details from there;
- I also want to be able to put a CD into the CD player on my PC, and get the details straight from there;
- To have the search ability available to customers on the internet.
Now, having some idea of the system requirements, we can start looking at the objects that we are going to be modelling in the database, and the attributes that these objects will have. Note that there will be some attributes that won't be needed to be modelled.
Each Album was recorded by an Artist, and was released by a Record Company with a Catalogue Number. I purchased each album from a Shop on a certain Date, and paid a certain Price for it. Each record has a number of Tracks, each of which are of a certain Duration, were written by one or more People, and appear on the record in a certain order. Each record will be on a certain Format, and if this is a CD will have a reference number allocated by the computer if I place it in the CD-ROM drive.
Having listed the data that we want to hold, we could just create a flat file database based on the above, but it wouldn't be very flexible. Therefore, we need to break the structure up into tables that are related to each other. A suggested layout is:
tblRecord: RecordID, BandID, FormatID, ShopID, RecordCompanyID, RecordTitle, RecordCatalogueNumber, RecordNotes, RecordImagePath, PurchaseDate, PurchasePrice, CDPlayerReference.
tblBand: BandID, BandName, BandNotes, BandImagePath.
tblTrack: TrackID, RecordID, TrackNumber, TrackMinutes, TrackSeconds, TrackWrittenBy, TrackNotes.
tlkpShop: ShopID, ShopName.
tlkpRecordCompany: RecordCompanyID, RecordCompanyName.
tlkpFormat: FormatID, FormatName.
A few notes on the design that I have chosen. Firstly, in some cases, there isn't a good natural key available. In the case of the record, the catalogue number, or the EAN-13 would be a good choice, if I wanted to go through and ensure that every time I entered a record into the database I entered this information. As I've got rather a lot of records to enter, I will just start off by entering the main details for the record, and as time goes by, I will enter more information about each record. I normally use an AutoNumber field as a Primary Key anyway, and keep it hidden from the user, rather than using a test field, as this should speed database performance, unless I can guarantee that the text field is going to be very short (i.e.. for a table of American States, the primary key could be the two letter abbreviation).
Secondly, I am choosing to store the duration of each track in two separate fields, rather than just using a Time field. In Access, any Date/Time field stores both Date and Time, even though it might not be showing it. Therefore, I find in certain cases that it is easier to use a different data type to the one that is automatically suggested.
Finally, at this stage in the creation of a database, it is worth considering what sort of data is going to be stored in each field, whether each field is required for all records, and if there is a default value for any field.
Copyright & Disclaimer
Last modified at 06/06/2006 15:02:09