Web Database Applications With Php And Mysql (2nd Edition) [Electronic resources] نسخه متنی

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 databasessuch as entity-relationship
(ER) modelinga 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.