 |  |
Appendix C. Modeling and Designing Relational Databases
Planning and designing a database is the essential first step to developing a web database application. In this appendix, we introduce database modeling and the techniques to convert a model into the SQL statements needed to create a database.
This appendix isn't intended to replace a course or book on relational databases. Modeling requirements with an entity-relationship model requires both patience and experience. Instead, we detail our thought processes in a case study that models the winestore requirements and converts these to SQL CREATE TABLE statements. Pointers to resources on modeling and database design are included in Appendix E.
C.1. The Relational Model
Relational database management systems, or RDBMSs, maintain, enforce, and use relationships between data. To illustrate the principles of relational databases, we use the winestore system requirements and descriptions from Chapter 1 as the basis for our examples.
C.1.1. Case Study: Relations in the Winestore
There are three essential types of data or entities that form the basis of the winestore. First, there is the wine itself: each wine has characteristics or attributes such as a name, a type, and a variety. Second, there is the customer, who has attributes such as a name, an address, and a phone number. Last, and importantly in selling wine online, is a customer purchase order. It is the order that forms a relationship between customers and wines.
An order is made when a customer purchases a quantity of wine. Consider an example. One of our customers, customer #37—we give our customers a number, so as not to confuse two customers who have the same name—purchases two bottles of wine #168, our 1996 Cape Mentelle Cabernet Merlot. The database stores this relationship as an order: customer #37 placed their fifth order with us, ordered wine #168, and required a quantity of two bottles. Figure C-1 shows a simple representation of this relationship.
Figure C-1. Customer #37 purchases two bottles of wine #168
There are several constraints in the order that may be obvious but are worth stating: there is only one customer #37, there is one wine we refer to as #168, and the next time the customer orders with us, it will be their sixth order. Relational databases can enforce many constraints on data stored, including ensuring in the example that an order can be made only by a valid customer and that only wines we stock can be ordered. When you represent data in a database, entities such as wine, customers, and orders are represented as tables or relations that group together related data.
There are some limitations to this model. One limitation is that an order consists of only one wine. There are several ways this problem can be resolved. Perhaps the most obvious approach is to create additional attributes in the order, such as wine2, quantity2, wine3, quantity3, and so on. The problem is where to stop: what is the maximum number of wines per order? And, if an order contains only one wine, how are the unused attributes processed? Indeed, just as if you were designing a spreadsheet, any report works only for that number of wines; any change means redevelopment of the report.
Another solution to the problem is to introduce a new table that stores the items that make up an order. This approach is subtle but solves the problems with the initial approach. How, then, do you know when to add attributes or when to add tables? Traditionally, this answer has been the somewhat technical explanation that the database should be normalized according to a set of rules; most acceptably designed databases are in the third-normal form. Fortunately, with the advent and refinement of simpler modeling techniques for designing databases—such as entity-relationship (ER) modeling—a well-designed database can be achieved by following simple rules.
We discuss ER modeling in the next section, as we focus on designing a workable winestore.
|  | |
| B.2. Hypertext Transfer Protocol |  | C.2. Entity-Relationship Modeling |
Copyright © 2003 O'Reilly & Associates. All rights reserved.