 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
Creating an Access Database - Step 2: The Tables
OK, we've sat down and thought about the design of the database, and now have some idea about what we are creating. We can now switch the computer on, and start Access. Now, if we thought about the design properly, we should only need to deal with the tables and relationships once.
Before we actually create any tables, it is an idea to change an option so that Access doesn't create duplicate table indexes. From the database window, select Tools|Options, and then click on the Tables/Queries Tab. Remove "ID;" from the AutoIndex on Import/Create. If we had left this in, Access would automatically create an index on any field with "ID" in the field name. Whilst this might be OK normally, I prefer to end any primary/foreign key with "ID", and if this is a primary key, Access will create a second index as well as the primary key index. Although Access allows you 32 indexes in a table, and you probably won't have that many fields in a tables, let alone indexes, the effects of the second indexes will become more apparent when you create queries.
Now we can start by creating the tables. I'm just going to list the attributes for each table here:
| Field Name | Data Type | Notes |
|---|---|---|
| RecordID | AutoNumber | Primary Key for the Table |
| BandID | Long Integer | Which band released the record (Foreign Key from tblBand) |
| FormatID | Long Integer | What format the record is on (Foreign Key from tlkpFormat) |
| ShopID | Long Integer | The shop that I bought the record from (Foreign Key from tlkpShop) |
| RecordCompanyID | Long Integer | The record company that released the record (Foreign Key from tlkpRecordCompany) |
| RecordTitle | Text | The title of the record |
| RecordCatalogueNumber | Text | The catalogue number for the record |
| RecordNotes | Memo | Notes for the record |
| RecordImagePath | Text | The path to a scanned cover |
| PurchaseDate | Date | The date of purchase |
| PurchasePrice | Currency | The purchase price |
| CDPlayerReference | Long | The reference number for a CD from the CDROM drive |
| Field Name | Data Type | Notes |
|---|---|---|
| BandID | AutoNumber | Primary Key for the Table |
| BandName | Text | Name of the Band |
| BandNotes | Memo | Notes about the band |
| BandImagePath | Text | The path to a picture of the band |
| Field Name | Data Type | Notes |
|---|---|---|
| TrackID | AutoNumber | Primary Key for the Table |
| RecordID | Long Integer | Which record the track is on (Foreign Key from tblRecord) |
| TrackNumber | Byte | The position of the track on the record |
| TrackMinutes | Byte | The duration of the track in minutes |
| TrackSeconds | Byte | The duration of the track in seconds |
| TrackWrittenBy | Text | Who wrote the track |
| TrackNotes | Text | Notes about the track |
| Field Name | Data Type | Notes |
|---|---|---|
| ShopID | AutoNumber | Primary Key for the Table |
| ShopName | Text | Name of the shop |
| Field Name | Data Type | Notes |
|---|---|---|
| RecordCompanyID | AutoNumber | Primary Key for the Table |
| RecordCompanyName | Text | Foreign Key from tblCustomer |
| Field Name | Data Type | Notes |
|---|---|---|
| FormatID | AutoNumber | Primary Key for the Table |
| FormatName | Text | Name of the shop |
When creating a table, I prefer to have the primary key as the first field, followed by any foreign keys, and then the rest of the fields. I normally try to use descriptive field names, made up of two or more words joined together without spaces. This has several uses. Firstly, it limits the chance of me using a reserved word, for example Date, and secondly, it helps me when I come back into the database several months later, by seeing a field that describes what it is.
Having created the tables, we can now relate the tables. From the main database window, select Tools|Relationships, and add all of the tables to the screen (after all, there's not much point in having tables that aren't related to anything else).
Create the following relationships between the tables:
| Field Name | Table Name | Relationship | Field Name | Table Name |
|---|---|---|---|---|
| RecordCompanyID | tlkpRecordCompany | One-Many | RecordCompanyID | tblRecord |
| FormatID | tlkpFormat | One-Many | FormatID | tblRecord |
| BandID | tblBand | One-Many | BandID | tblRecord |
| ShopID | tlkpShop | One-Many | ShopID | tblRecord |
| RecordID | tblRecord | One-Many | RecordID | tblTrack |
The resulting relationships should look something like:
Copyright & Disclaimer
Last modified at 06/06/2006 15:02:10