Databases Reference
In-Depth Information
most general many-to-many-to-many ternary relationship between salespersons,
customers, and products. It means that we know which salesperson sold which
product to which customer.
Each sale has
intersection data
consisting of the date of the sale and the
number of units of the product sold. Often, rather than trying to consider these
as ternary relationships, it's easier to look at them as sets of binary relationships.
3.3.4 Breaking Down Many-to-Many Relationships
One of the keys to having a well-designed data model and database is how well
you've handled many-to-many relationships. Some designers try to find excuses
not to use them. If the relationships are overlooked, you can end up stuffing a
table with unnecessary, duplicated data. If you try to avoid using many-to-many
relationships, you end up with a poorly designed model that wastes space and
results in less than optimum performance.
Generally, we think of attributes as facts about entities. Each salesperson
has a salesperson number, a name, a commission percentage, and a year of
hire. At the entity occurrence level, for example, one of the salespersons has
salesperson number 528, the name Jane Adams, a commission percentage of
15 percent, and the year of hire of 2003. In an E-R diagram, these attributes
are written or drawn together with the entity, as in the E-R diagrams you have
seen. This certainly appears to be very natural and obvious. Are there ever any
circumstances in which an attribute can describe something other than an
entity?
Consider the many-to-many relationship between salespersons and prod-
ucts back in Figure 3-12c. As usual, salespersons are described by their
salesperson number, name, commission percentage, and year of hire. Products
are described by their product number, name, and unit price. But what if there
is a requirement to keep track of the number of units, call it “quantity,” of a
particular product that a particular salesperson has sold? Can we add the quan-
tity attribute to the product entity box? No, because while a particular product
has a single product number, product name, and unit price, there will be lots
of quantities, one for each salesperson selling the product. Can we add the
quantity attribute to the salesperson entity box? No, because while a particular
salesperson has a single salesperson number, salesperson name, commission
percentage, and year of hire, there will be lots of quantities, one for each prod-
uct that the salesperson sells. It makes no sense to try to put the Quantity
attribute in either the salesperson entity box or the product entity box. While
each salesperson has a single salesperson number, name, and so forth, each
salesperson has many quantities, one for each product he sells. Similarly, while
each product has a single product number, product name, and so forth, each
product has many quantities, one for each salesperson who sells that product.
But an entity box in an E-R diagram is designed to list the attributes that
