E.1 The Relational Model

Relational database management systems maintain, enforce, and use relationships between data to answer complex queries. To illustrate the principles of relational databases, we use the winestore system requirements and descriptions from Chapter 16 as the basis for our examples.

E.1.1 Case Study: Relationships 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 and an address. Last (and importantly in selling wine!) is a customer purchase order. It's the order that forms a relationship between customers and wines.

An order is made when a customer purchases a quantity of wine, and this creates a relationship between a customer, a wine, and an order. 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 E-1 shows a simple representation of this relationship.

Figure E-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 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 need to be represented as tables or relations that group together related data. It's important that you make the right choices in deciding whether to add tables, relationships, or attributes to your database. If you get it wrong, then your application will be inflexible: you'll have trouble updating data, adding new information, or keeping the data consistent.

Suppose, after thinking about our example in Figure E-1, that we decide to create two tables, customer and order. Let's assume customers only have a customer identifier, a name, and an address. Let's also assume that orders have a customer identifier, an order identifier, a wine identifier, and a quantity. Here's the statements we use:

CREATE TABLE customer (   cust_id int(5) NOT NULL,   name varchar(50),   address varchar(50),   PRIMARY KEY (cust_id) ); CREATE TABLE order (   cust_id int(5) NOT NULL,   order_id int(5) NOT NULL,   wine_id int(5),   quantity int(3),   PRIMARY KEY (order_id) );

To illustrate the problem of database design, consider a serious limitation of our simple model: an order has only one wine_id and so it consists of bottles of only one wine. There are several ways this problem can be resolved. Perhaps the most obvious approach is to add additional attributes to the order table, such as wine_id2, quantity2, wine_id3, 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? To work with this design, you'll need lots of if clauses in your PHP scripts.

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 and we discuss it in detail later. 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. 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 carefully following simple rules and some trial and error. You don't need to know anything about normalization.

We discuss ER modeling in the next section, as we focus on designing a workable winestore.