Databases Reference
In-Depth Information
One problem is that this process can be taken to the extreme. Consider cus-
tomer addresses for example. It's likely that you will have several customers in
the same state. You could create a separate STATE table to contain this informa-
tion and create a relationship between the CUSTOMER and STATE tables, but it's
probably less work and requires less storage space to just store the 2-character
state abbreviation with each customer.
The other problem is that a design that makes for efficient data entry does
not always make for efficient data retrieval. During normalization, you tend to
break a table down into smaller, related tables. There is a good possibility that
at least some of your queries will require you to recombine that data into a sin-
gle result. This is done through a query process known as
joining,
where you
combine the data from two tables based on a linking column or columns. Typ-
ically, you will combine two related tables based on the foreign key, but that's
not the only possibility. This can be a resource-intensive process, becoming more
intensive as more tables are added.
This can be an issue in any database, but tends especially to be a problem
in decision support databases where the majority of the database activity relates
FOR EXAMPLE
Finding New Tables
It's fairly common to “discover” new tables during the normalization process.
Your E-R diagram includes an ORDER entity. For each order, you have the
customer placing the order, the employee writing up the order, the order
date, order number, and other information that applies to the order as a
whole. You also have information about individual line items, such as the
item ID, quantity, and selling price. There's no reason, usually, to store the
extended total (quantity times selling price) because that can be calculated
whenever you need it.
If you create an ORDER table, for it to be properly normalized, you will
need a row for each line item in the order. That means that the customer,
employee, order date, and any other general information about the order are
also repeated for each line item. This could result in a significant amount
of data and wasted space. A better solution is to have two tables. One, call
it ORDERHEAD, contains the information that applies to the order as a
whole. The other, call it ORDERITEM, contains the information for each line
item. You would use the Order Number as the identifier in ORDERHEAD,
and also use it as the foreign key in ORDERITEM to maintain the relation-
ship between the two tables.
