Specifications

Choose Sensible Keys
Make sure that the keys you choose are unique. In this case, we’ve created a special key for
customers (CustomerID) and for orders (OrderID) because these real-world objects might not
naturally have an identifier that can be guaranteed to be unique. We don’t need to create a
unique identifier for books—this has already been done, in the form of an ISBN. For
Order_Item, you can add an extra key if you want, but the combination of the two attributes
OrderID and ISBN will be unique as long as more than one copy of the same book in an order
is treated as one row. For this reason, the table Order_Items has a Quantity column.
Designing Your Web Database
C
HAPTER 7
7
DESIGNING YOUR
WEB DATABASE
179
OrderID
ORDER_ITEMS
Quantity
11
22
21
31
ISBN
0-672-31697-8
0-672-31745-1
0-672-31509-2
0-672-31697-8
410-672-31745-1
420-672-31509-2
410-672-31697-8
FIGURE 7.6
This design makes it easier to search for particular books that have been ordered.
Think About the Questions You Want to Ask the
Database
Continuing from the last section, think about what questions you want the database to answer.
(Think back to those questions we mentioned at the start of the chapter. For example, what are
Book-O-Rama’s bestselling books?) Make sure that the database contains all the data required,
and that the appropriate links exist between tables to answer the questions you have.
Avoid Designs with Many Empty Attributes
If we wanted to add book reviews to the database, there are at least two ways we could do this.
These two approaches are shown in Figure 7.7.
ISBN
BOOKS
Author
ISBN
BOOK_REVIEWS
Review
Title Price
0-672-31687-8 Michael Morgan Java 2 for Professional Developers 34.99
0-672-31745-1 Thomas Down Installing Debian GNU/Linux 24.99
0-672-31509-2 Pruitt, et al. Teach Yourself GIMP in 24 Hours 24.99
Review
FIGURE 7.7
To add reviews, we can either add a Review column to the Books table, or add a table specifically for
reviews.
10 7842 CH07 3/6/01 3:34 PM Page 179