Visual Basic Database Tutorial - Part 5, Page 4
So, in summary, it's good practice to throw anything you may need to store multiple values for, in a separate table. You can then link that information back to its 'owner' via a 'key', which is typically some form of ID number.
So let's put this theory into practice. Start Microsoft Access and create a new database say, c:\surgery.mdb
Click on the Tables tab, hit the New button, select Design View and click OK.
You should be presented with the table design window, which allows you to define the fields you want in your table. It should look a little like this at the moment:
Your cursor should be flashing in the first 'Field Name' box at the moment. We'll call this field 'OwnerID' so type it in and press Tab.
You should now be in the 'Data Type' box. This is where you tell Access what type of information you want the field to hold. For example, you could tell it to hold normal Text, or perhaps a Date, or perhaps a Number, or perhaps... an AutoNumber.
Select the latter. An AutoNumber is an automatically generated number that will be inserted into the field each time a new record is entered into the table. And it's completely unique, too which is just what we want for the OwnerID field.
Now this field will be the 'Primary Key' in other words, that main number which uniquely identifies an owner. And to make sure Access knows this, click on the 'Primary Key' button on your toolbar. You should see a small key appear beside the Field Name.
Click down into the next 'Field Name' box and enter 'OwnerName'. Tab across and select the 'Text' data type. The bottom half of your screen should look like this:
This lists all the properties of that one field. You might, for example, change the 'Default Value' property to 'John' meaning that 'John' will be automatically inserted into that field as a default name every time you add a record.
Or you may change the 'Field Size'. At the moment, our field will only hold up to 50 characters. But we're going to change that to allow for owners with particularly long names go on, up the value to 100 characters!
Also, change the 'Required' property to Yes. That means a user cannot add a new record to the table without this field OwnerName being completed.
Add another Field Name and call it 'Address'. Again, change the Field Size to 100 and the Required property to Yes.
Your screen should look like this right now:
Click File and Save. Enter the name "Owners" and click OK. Now close the table and follow the exact same process to create a table with the following specifications:
- Table Name - Pets
- 1st Field Name - OwnerID
- 1st Field Data Type - Number
- 2nd Field Name - PetName
- 2nd Field Data Type - Text
- 3rd Field Name - Breed
- 3rd Field Data Type - Text
When saving the table, you may get prompted to add a Primary Key just click No. This is the secondary table, which contains the Foreign Key (OrderID) we mentioned earlier.
We've almost completed the database design; we've created two tables, with an AutoNumber in one and a number field for that AutoNumber in the second. All that remains is for us to tell Access that there is a relationship between the first Owners table and the secondary Pets table.
Return to the main Database window and click the Relationships button on the toolbar.
You'll be prompted with a box asking which tables you wish to show in your Relationships diagram. Double-click on both Owners and Pets, then click Close. Your screen should look something like this:
At this point, we want to tell Access there is a relationship between the Owners table and Pets table. Drag the OwnerID field in the Owners table over to the OwnerID field in the Pets table and let go of the mouse button.
You should be prompted with the following:
Notice that Access has determined the relationship type as being 'One-To-Many' meaning there will be one occurrence of the OwnerID number in the first Owners table and possibly numerous instances of that number in the Pets table.
And that's right one owner can have multiple pets. Just as one company department may have many employees. Just as each customer order may have numerous individual order items.
So this One-To-Many relationship isn't justuseful in the vetinary world.
Check the 'Enforce Referential Integrity' button; this will ensure your data stays in tip-top condition. In other words, your users won't be allowed to enter a value in the OwnerID field of the Pets table that doesn't exist in the Owners table. After all, you can't really associate a pet with an owner that doesn't exist!
All the referential integrity thing does is enforce that rule.
Click OK. Your screen should look something like this:
Click on File, Save to store the relationship.
And that's it... you've completed your database design!
Now if you wanted to be a real boffin, you could probably put all the breeds in a separate table and instead of inserting a text value in the Breed field insert a number, a Foreign Key linking it back to a Breed description in another table. Don't forget that storing a number is much more efficient than storing a piece of text. And it's certainly less prone to typis.
[Ed: Err... Typos]
But we'll leave that till another day. For now, give yourself a bally hard pat on the back... you've completed the database design! And it's absolutely mega-cool! Hah, move over Bill Gates...