I want to take some time to give some background on the table
structure for EmilioFrancisco.com. There are a few things I
want to point out that actually fit a structure that ends up really
being quite general.
So these tables should be considered to be generally representative, filling quite a bit of detail, but leaving out others. For example, the nature of the EmilioFrancisco.com business is that they are dealing with international companies. Our typical contact information structure is really configured for the United States. But these sorts of things can be readily modified as you know more about international businesses.
I will go through giving the data dictionaries for a number of different tables. We will start with the customers table. Remember, EmilioFrancisco.com sells to other businesses not to individual people. This impacts our table structure somewhat.
|Now we will work with the
vendors table. Remember, EmilioFrancisco.com is
basically an import distributor for three shoe companies.
But we need to design our tables and database so that it can adapt if they add more vendors or reduce them.
Again we oversimplify this
|Now we will work with the
products table. We have all of our source vendor
information in the vendors table so we don't need to repeat
information in this product table other than to give the foreign key
that refers back to the vendor company.
In this case I've also decided to go with the identifiers for the products used within EmilioFrancisco.com, which they likely pick up from their vendors. In most instances this is preferable to using an autonumber since the product identifier is going to be much more universal and available.
|It is very likely that they will keep much more
information about quantities on hand, time to fill and order,
typical order quantities and any discounts they get for larger
orders and so on. But I hope this table information will help
develop other insights in addition to developing an understanding
that fields and table structures really do need to synchronize with
Now we move on to the orders table.
Think about the Madam Curry situation where we had the orders table list individual items. While it is not WRONG, there are two main weaknesses to this approach.
Madam Curry was used to barely selling any books at all, and so listing every item individually, even if they were ordered as a group, doesn't hurt her very much. But in almost all other settings you want to have information about the
So, while developing our database for EmilioFrancisco.com, we will have the orders table contain information about the order as a whole. Then we will have an order_item table that contains information about individual items in the order. This sort of structuring is very common.
So now we present a fairly sparse order table data dictionary.
|The data dictionary for the order_item table follows. Notice it is mostly a repository of foreign keys that refer back to more complete information in other tables.|
|So hopefully this overview of an important
segment of the tables in the emilio_francisco.mdb database has
helped you gain even more insight.
For our final diagram we reproduce the relationship diagram from within Access.
|Listing the parent-to-child or one-to-many
relationships in appropriate order gives.
Unfortunately, Access doesn't do a better job of representing the direction of the one-to-many relationship. So you can see that some of these arrows are left to right and others are really right to left.