Datasheet

Chapter 1
26
One-to-many relationships between the entities have been denoted by creating straightforward
one-to-many relationships between the tables:
tblCustomer (1) (n) tblSales
A sale can only involve one customer, but a customer can have more than one sale
tblIceCream (1) (n) tblSales
Only one type of ice cream can be sold in a particular sale, but an ice cream can be sold more than once
Many-to-many relationships have been handled by creating two intermediate tables
(
tblIceCreamIngredient and tblSupplierList) and placing one-to-many relationships on
either side of the intermediate table:
tblIce
Cream
(1) (n)
tblIceCream
Ingredient
(n) (1) tblIngredient
An ice cream is composed of many ingredients and the same Ingredient can be used in many ice creams
tbl
Supplier
(1) (n) tblSupplier
List
(n) (1) tblIngredient
A supplier can provide many ingredients and the same ingredient could be provided by many suppliers
This use of intermediate tables is the standard way in which we join two tables together when
the two tables have a many-to-many relationship. It is part of a process called normalization,
which is a series of steps you go through to make sure your database is designed correctly. This
process is really beyond the scope of this book, but there are plenty of books specializing in it.
One such book is (as we've mentioned earlier) 'Database Design for Mere Mortals', Michael J.
Hernandez, Addison-Wesley, ISBN 0-201-69471-9.
Typical Dilemmas Regarding Data Storage
A few features of this database structure are worthy of note. Firstly, note the duplication of the
ContactName attribute in the tblSupplier, tblCustomer, and tblSales tables. This is
deliberate and caters for the fact that although there is one primary contact for each supplier
and for each customer, the Ice Cream Shop also wants to be able to assign separate contacts to
individual sales.