Databases Reference
In-Depth Information
Defining Uniqueness
Since, as we have just seen, a many-to-many relationship can appear to be a
kind of an entity, complete with attributes, it also follows that it should have a
unique identifier, like other entities. In its most basic form, the unique identi-
fier of the many-to-many relationship or the associative entity is the combina-
tion of the unique identifiers of the two entities in the many-to-many relation-
ship. So, the unique identifier of the many-to-many relationship of Figure 3-15
or the associative entity of Figure 3-16 is the combination of the Salesperson
Number and Product Number attributes.
Sometimes, an additional attribute or attributes must be added to this com-
bination to produce uniqueness. This often involves a time element. For exam-
ple, if we wanted to keep track of the sales on a weekly basis, we would have to
have a date attribute or a week number attribute as intersection data and the
unique identifier would be Salesperson Number, Product Number, and Date. If
we want to know how many units of each product were sold by each salesper-
son each week, the combination of Salesperson Number and Product Number
would not be unique because for a particular salesperson and a particular prod-
uct, the combination of those two values would be the same each week! Date
must be added to produce uniqueness, not to mention to make it clear which
week a particular value of the Quantity attribute applies to a particular sales-
person product combination.
The third and last possibility occurs when the nature of the associative entity
is such that it has its own unique identifier. For example, a company might
FOR EXAMPLE
Describing Relationships
It is common to have multiple relationships between one entity and other
entities. Consider this situation. Your company does telephone sales to estab-
lished customers. Each customer has a customer ID. Each employee is iden-
tified by an employee ID. Whenever an order is placed, it must include both
the employee ID of the employee creating the order and the customer ID of
the customer placing the order.
Here, we have two separate one-to-many relationships. One is
between customers and orders—each customer can have multiple orders.
The other is between employees and orders—each employee can create
multiple orders. Even though they are related, there is no need to explic-
itly identify a relationship between employees and customers. This is
taken care of for you, because the two are associated through the order
entity.
