Datasheet

16
Chapter 1: Introducing T-SQL and Data Management Systems
Relationships can be defined as follows:
One - to - zero or more
One - to - one or more
One - to - exactly - one
Many - to - many
The many - to - many relationship requires three tables because a many - to - many constraint would be
unenforceable. An example of a many - to - many relationship is illustrated in Figure 1 - 10 . The necessity for
this relationship is created by the relationships between your entities: In a single sale many products can
be sold, but one product can be in many sales. This creates the many - to - many relationship between the
Sale table and the Product table. To uniquely identify every product and sale combination, you need to
create what is called a linking table . A linking table is simply another table that contains the combination
of primary keys from the two tables, as illustrated in Figure 1 - 10 . The Order table manages your many -
to - many relationship by uniquely tracking every combination of sale and product.
❑
❑
❑
❑
Figure 1-10
As an example of a one - to - one relationship, suppose that you want to record more detailed data about a
sale, but you do not want to alter the current table. In this case, you could build a table called SaleDetail to
store the data. To ensure that the sale can be linked to the detailed data, you create a relationship between
the two tables. Because each sale should appear in both the Sale table and the SaleDetail table, you would
create a one - to - one relationship instead of a one - to - many, as illustrated in Figures 1 - 11 and 1 - 12.
Figure 1-11
Figure 1-12
CH001.indd 16CH001.indd 16 3/26/10 11:35:40 AM3/26/10 11:35:40 AM