Tutorial on Database Schema - Slide 7 - Customers at Amazon.com
Back Next Slide
- Customers at Amazon.com
Addresses, Payments and Many-To-Many Relationships
Customers and Addresses
At Starbucks, we give very little information about ourselves as Customers.
If we pay by cash, we give no details at all.
Amazon.com, on the other hand, needs to know a good deal more about us.
For example, they need to store details about our addresses, and we can have many of these.
In our Database Schema,we could have a Billing Address, a Residence Address and more than one Delivery Address.
In passing, let me say that the existence of a separate Addresses Table is often a distinguishing characteristic between a Database Schema with
limited scope,and one intended for use in a mission-critical capacity.
Therefore we store Addresses in a separate Table, instead of repeating Address details many times in Customer Tables.
This has many benefits. It allows us to validate the Address against files of recognised Addresses.
This has become a standard approach so that many commercial products are available to help in this validation.
In addition, maintaining a separate Address Table helps in tracking changes of Customer Addresses.
The Primary Key in the Customer_Addresses table is the combination of the Customer ID, the Address ID,
and the Date when the Customer moved in.
This works because the Customer cannot move in to the same Address more than once on the same Date.
Of course, they could move out and back in on another date, and our design covers that situation.
Many-To-Many Relationships
And, of course, as we saw before, this is an example where a Many-to-Many relationship is found.
A Customer can have many Addresses, and many Customers can live at the same Address.
We resolve this many-to-many by an intermediate table, shown in the diagram as 'Customer_Addresses'.
In a similar way, there is a many-to-many relationship between Customers and Payment Methods.
A Customer can have many Payment Methods, such as different Credit Cards, as well as Cash and Check.
And, of course, the same Payment Method can be used by many Customers.
We resolve the many-to-many Customer Payment Relationship with an intermediate table,
which is called 'Customer_Payment_Methods' in the diagram.
Customer Payment Methods
There is a number of Payment Methods, which are stored as Reference Data,
These will usually be Cash (at Starbucks) or Credit Card (at Amazon.com).
The Customers_Payment_Methods Table can store Credit Card details.
Standards for Reference Data
Where we use Reference Data, it is good practice to comply with relevant national or international Standards.
For example, in the Addresses Table, we can see a field called 'iso_country_code'.
The ISO prefix emphasises the fact that we have adopted conventions endorsed by the International Standards Organisation,('ISO').
Then, if we want to use our Database Schema on an international scale, our approach will be 'future proof'.
© DataBaseAnswers.org 2006