Some Background
on
EmilioFrancisco.com

 

Some Background.  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.

 

customers

id_customer autonumber
firm_name text
address text
city text
state text
zip text
contact_person text
contact_phone text
contact_fax text
contact_email text
   
   

 

Now we will work with the vendors table.  Remember, EmilioFrancisco.com is basically an import distributor for three shoe companies.
  • Emilio Francisco
  • Gianni Versazzi
  • Dolce Durenza

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

 

vendors

id_vendor autonumber
vendor_name text
address text
city text
state text
zip text
contact_person text
contact_phone text
contact_fax text
contact_email text
   

 

 

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.

 

products

id_product text
product_name text
fk_id_vendor text
number_on_hand number
price currency
   

 

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 business rules.

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.

  • People often order multiple items at the same time in a single overall order.  The approach used in the Madam Curry database does not really reflect this eventuality very well.
  • People can order more than one of a given item.  It can be worthwhile to have a field that specifies quantity.

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

  • order as a whole
  • individual or multiple items in the order

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.

 

orders

id_order autonumber
fk_id_customer number
order_date date/time
total_cost currency
   
   

 

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.

 

order_item

id_order_item autonumber
fk_id_order number
fk_id_product number
quantity number
   
   

 

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.
  • customers to orders is one-to-many based on id_customer in the customers table and fk_id_customer in the orders table
  • orders to order_item is a one-to-many relationships based on id_order in the orders table and fk_id_order in the order_item table
  • products to order_item is a one-to-many relationship based on the id_product in the products table and fk_id_product in the order_item table
  • vendors to products is a one-to-many relationship based on the id_vendor in the vendors table and fk_id_vendor in the products table

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.