The Tables for a Multi-Product MadamCurry.com
We talked about MadamCurry.com in a previous week. At that
time, Madam Curry was only offering her customers a single book or
the possibility to join her email list. But we will expand on
this somewhat now that she has added two smaller cookbooks to her
more encyclopedic effort. So the titles of her books are
The entities we will focus on in this example are
Notice that I break an HPM rule by using plural nouns.
For the sake of saving you the effort of going back to previous webpages, we will delineate the fields we want for the e-mail list.
The data for the purchasers will be largely similar, but we need to differentiate between shipping and billing information. Notice, I have modified the table contents slightly to more accurately reflect the situation.
|Now we need a data dictionary for the table for the products or books.|
|I have set the data type for the copyright field
as text. This is because the date/time data type has quite a
few restrictions in Access. Copyrights are almost always
entered as just the year of the copyright.
But since our purchasers may by more than one book, we really should be keeping track of their orders.
|Now I want to specify the relationships
between these tables. I am going to try and lead you
through developing these relationships in Access.
Make sure you are located on the tables within your madam_curry.mdb
Select the Tools menu.
In the tools menu select the Relationships item.
You should have a small blank screen open unless you have already developed some other relationships..
Right click in this blank form to bring up a popup menu.
Select the Show Table item in this popup menu.
Then select each of the tables, by clicking on their name and then clicking on the Add button.
This will make the tables available for use in the form.
Now you want to start connecting keys in one table to foreign keys in another table until you get something like the following image.
|Notice I have the tables from left to right in
email_list - purchasers - orders - books
You start by clicking on the key in one table and then while holding down the button you drag to the foreign key you want in the next table. This way you create the relationships by selecting the create button.
Constructing these relationships is important for developing forms, subforms, queries and reports that reflect the inter-relationships among the tables and data.
You should click on the following link to download my copy of madam_curry.mdb to save to your computer. Then you can experiment with it as you'd like.