Datasheet

Figure 1-3: The PhoneBookEntry table with a primary key
Showing one-to-many links as a line with a key at one end and linked circles at the other (the “infinity”
symbol) is just one way to display this information. You may also see lines with a 1 at one end and an
ellipsis at the other. In this book, however, you’ll see the format shown here throughout.
In this scheme it is typically a good idea to include a further column in the
OrderProduct table —
Quantity. This enables a product that appeared multiple times in a single order to be represented using
a single row in
OrderProduct, rather than several, where the number of rows would be the quantity.
Without the
Quantity column, things could quickly get out of hand for large orders!
One last thing to note here is the concept of referential integrity. Because these relationships are defined
as part of the database schema, the DBMS is capable of enforcing them. This means, for example, that
you can choose for the DBMS to prevent the deletion of a row that is referred to by another row.
Alternatively, you could choose to have the DBMS delete any referenced rows when a row is deleted
(known as a cascaded delete).
Normalization
Normalization is a fairly advanced topic, but one you need to be aware of from the outset. It refers to
the process of ensuring that little or no data in a database is duplicated. Another way of looking at this is
that it is the process of organizing the structure of data and data tables so that the most efficient method
of storage is used. What happens, for example, when a single customer places more than one order?
With just an order table you’d end up in a situation where customer details were duplicated because
they’d need to be included in each and every order made by the customer. It would be far better to add
an additional table for customers, which could be linked to multiple orders.
To extend the example: how about customers with multiple addresses? This might happen if a customer
wants to send an item directly to a friend. Here, a further table containing addresses is required. But
hold on — if an order is associated with a customer, and a customer has multiple addresses, how do you
tell which address the order is supposed to be sent to? Clearly, even simple databases can become much
more complicated quickly — and often there are multiple solutions to problems. The subject of database
organization and normalization is one that you will return to many times in later chapters.
In some circumstances redundancy, that is, the duplication of information, can be beneficial. This is par-
ticularly true when speed is crucial because there is an overhead associated with finding a row in one
table based on a foreign key in another. This may be negligible, but in large-scale, ultra-high perform-
ance applications, it can become an issue.
7
Database Fundamentals
44063c01.qxd:WroxBeg 9/12/06 10:30 PM Page 7