Specifications

With this design, we need to insert Julie’s details every time we take an order, so each time we
must check and make sure that her details are consistent with the existing rows in the table. If we
don’t check, we might end up with two rows of conflicting information about Julie. For example,
one row might tell us that Julie lives in Airport West, and another might tell us she lives in
Airport. This is called an insertion anomaly because it occurs when data is being inserted.
The third kind of anomaly is called a deletion anomaly because it occurs (surprise, surprise)
when we are deleting rows from the database. For example, imagine that when an order has
been shipped, we delete it from the database. When all Julie’s current orders have been ful-
filled, they are all deleted from the Orders table. This means that we no longer have a record of
Julie’s address. We can’t send her any special offers, and next time she wants to order some-
thing from us, we will have to get her details all over again.
Generally you want to design your database so that none of these anomalies occur.
Use Atomic Column Values
This means that in each attribute in each row, we store only one thing. For example, we need to
know what books make up each order. There are several ways we could do this.
We could add a column to the Orders table which lists all the books that have been ordered, as
shown in Figure 7.5.
Using MySQL
P
ART II
178
OrderID
ORDERS
CustomerID Amount Date
1 3 27.50 02-Apr-2000
2 1 12.99 15-Apr-2000
3 2 74.00 19-Apr-2000
4 3 6.99 01-May-2000
Books Ordered
0-672-31697-8
0-672-31745-1, 0-672-31509-2
0-672-31697-8
0-672-31745-1, 0-672-31509-2, 0-672-31697-8
FIGURE 7.5
With this design, the Books Ordered attribute in each row has multiple values.
This isn’t a good idea for a few reasons. What we’re really doing is nesting a whole table
inside one column—a table that relates orders to books. When you do it this way, it becomes
more difficult to answer questions like “How many copies of Java 2 for Professional
Developers have been ordered?” The system can no longer just count the matching fields.
Instead, it has to parse each attribute value to see if it contains a match anywhere inside it.
Because we’re really creating a table-inside-a-table, we should really just create that new table.
This new table is called Order_Items and is shown in Figure 7.6.
This table provides a link between the
Orders table and the Books table. This type of table is
common when there is a many-to-many relationship between two objects—in this case, one
order might consist of many books, and each book can be ordered by many people.
10 7842 CH07 3/6/01 3:34 PM Page 178