Datasheet

both important and extremely useful. For example, in a sales database you might want to record both
the products on sale and the orders placed for products. You can envisage a single table containing all
this information, but it is far easier to use multiple tables — one for products, and one for orders. Each
row in the orders table would be associated with one or more rows in the products table. An RDBMS
would then allow you to retrieve data in a way that takes this relationship into account — for example,
using the query “Fetch all the products that are associated with this order.”
Relationships between items in different tables can take the following forms:
❑ One-to-one relationship: One row in one table is associated with a row in a separate table, which
in turn is associated with the first row. In practice, this relationship is rare because if a one-to-
one relationship is identified, it usually means that the data can be combined into a
single table.
❑ One-to-many and many-to-one relationships: One row in one table is associated with multiple
rows in a separate table. For example, if a list of products were divided into categories (where
each product was associated with a single category), then there would be a one-to-many rela-
tionship between categories and products. Looking from the other direction, the relationship
between products and categories is many-to-one. In practice, one-to-many and many-to-one rela-
tionships are the same thing, depending on which end of the relationship you are looking at.
❑ Many-to-many relationship: Rows in one table are freely associated with rows in another table.
This is the relationship you have in the products and orders example because an order can con-
tain multiple products, and products can be part of multiple orders.
When considering relationships, the importance of keys is immediately obvious. Without being able to
uniquely identify rows it would be impossible to define a meaningful relationship. This is because asso-
ciating a row in one table with a row in another table might actually associate other rows with each other
by implication.
One-to-many relationships are implemented by including a foreign key field in the table at the many
end of the relationship. For example, to link products with categories you add a field to the product
table that acts as a foreign key to link product rows with category rows. The value of a foreign key in a
row in one table typically matches the value of a primary key in another table — in fact, the columns
used for primary and foreign keys are often given the same name.
The implementation of many-to-many relationships typically involves using a third, linking table. In the
products/orders example, a single row in the product table may be associated with multiple records in
the linking table, each of which is associated with a single order. Conversely, each row in the order table
may be associated with multiple rows in the linking table, each of which is associated with a single row
in the product table. In this situation, the linking table must contain two foreign keys, one for each of the
tables it is linking together. Unless required for other reasons, such as when the linking table contains
additional columns relating to the linkage, or represents real data in its own right, there is often no need
for you to include a primary key in the linking table.
Figure 1-3 shows four tables illustrating these relationships. Depending on how you look at it, this diagram
shows three one-to-many relationships (
ProductCategory to Product, Product to OrderProduct, and
Order to OrderProduct), or one one-to-many relationship and one many-to-many relationship (Product
to Order). To simplify things, the tables don’t show column data types or whether columns are nullable.
6
Chapter 1
44063c01.qxd:WroxBeg 9/12/06 10:30 PM Page 6