Datasheet

Designing Applications
27
Secondly, note the fact that the tblSupplier and tblCustomer tables have identical
structures. Whenever we see this in a database structure it should alert us to the fact that what
we have represented as two discrete entities might instead be represented as a single entity. So
what is it that differentiates a supplier from a customer? Obviously, we buy from suppliers and
customers buy from us. But is that sufficient reason for treating them as separate entities? After
all, what happens if a supplier is also a customer? If this was to occur, and we were maintaining
separate tblSupplier and tblCustomer tables, then changes to, say, the address of the
company involved would necessitate a change to both the tblSupplier and
tblCustomer tables.
A better alternative might be to combine the two tables into a generic tblCompany table:
tblCustomer
CustomerID
CompanyName
Address
City
State
ZipCode
Country
Phone
Fax
Email
Web
ContactName
Supplier
tblIceCream
IceCreamID
IceCream
Description
Price
Picture
tblSales
SalesID
fkCustomerID
fkIceCreamID
Quantity
DateOrdered
DateDispatched
DatePaid
AmountPaid
ContactName
tblIngredient
IngredientID
Name
Description
Unit
UnitsInStock
ReOrderPoint
tblSupplierList
fkSupplierID
fkIngredientID
PricePerUnit
LeadTime
tblIceCreamIngredient
fkIngredientID
fkIceCreamID
Quantity
1
1
1
This is actually the database design that has been employed in the Ice Cream Shop database
that accompanies this book. The tblCompany table now holds details of both suppliers and
customers. In order to distinguish between those companies that appear in the tblCompany
table (because they are either customers or suppliers) we have added a new
Supplier field to
the table. This field has a Yes/No data type and we will use to indicate whether the company
should appear in supplier lists.
This is not the only way that we could have chosen to implement the physical design of the
database. There is often no one correct database design. A fully normalized design might please
the relational database theorists, but they are not the ones who will have to maintain the
database or account for its performance in a production environment. Database design, as with
most aspects of system design and development, is all about achieving the best compromise.
There is nothing inherently wrong with denormalizing a database and it can be an excellent
tool for increasing query performance. However, before you decide to denormalize a database,
you should ensure that it doesn't introduce any significant data anomalies and that either: