Chapter 3: Designing Bulletproof Databases
99
FIGURE 3.5Second normal form: The OrderID field connects these tables together in a one-to-many relationship.
Related recordsEach field in tblBookOrders4 is dependent on the OrderID field and appears only once for
each order that is placed. The OrderID field in tblBookOrderDetails does not serve as the
primary key for tblBookOrderDetails; instead, it’s a foreign key. In fact, tblBookOrder-
Details doesn’t even have a primary key, but one could be easily added.The data in tblBookOrders4 and tblBookOrderDetails can be easily updated. If a book-
store cancels a particular book title in an order, the corresponding record is deleted from
tblBookOrderDetails. If, on the other hand, a bookstore adds more books to an order, new
records are added to tblBookOrderDetails to accommodate the additional titles, or the
Quantity field can be modified to increase or decrease the number of books ordered.Breaking a table into individual tables, each of which describes some aspect of the data, is called
decomposition. Decomposition is a very important part of the normalization process. Even though
the tables appear smaller than the original table (refer to Figure 3.2), the data contained within the
tables is the same as before.It’s easy to carry decomposition too far — creating only as many tables as are required to fully
describe the data set managed by the database. When decomposing tables, be careful not to lose
data. For example, if the tblBookOrders4 table contained a SellingPrice field, you’d want
to make sure that field was moved into tblBookOrderDetails.A developer working with the bookstore tables is able to use queries to recombine the data in
tblBookOrders4 and tblBookOrderDetails in new and interesting ways. It’d be quite easy
to determine how many books of each type have been ordered by the different customers, or how
many times a particular book has been ordered. When coupled with a table containing information
such as book unit cost, book selling price, and so on, the important financial status of the book
wholesaler becomes clear.