Entity Relationship Modeling - A Useful Relation
(Page 9 of 21 )
This is a very useful relationship: It implies what we call an "intersection entity", which will give an entity holding all legal combinations of orders and customers. This many-to-many relationship is much used in an early stage of analysis to indicate just that. This is such a useful relationship that I will evaluate it now. We return to the model of business contact and contact type we had earlier:
The way this is constructed, it only allows for a business to exist once. If we want a customer also to be a supplier, he must get a new contact id, and all information must be re-entered. We can (partly) solve it by making the primary key a combination of contact id and contact type:
Note the vertical bar on the crow’s foot: It indicates that the relationship is part of the primary key in contact id. This is one of the most common errors being made in ER modeling.
This is a violation of the Second Normal Form. Name, telephone and entered date have nothing to do with contact type, only with business contact. We see that we are not able to properly represent the fact that a business contact may be a number of contact types. Here we can take full use of the many-to-many relationship capabilities:
This relationship says that a business contact MAY BE categorized as zero or more contact types, while a given contact type MAY BE a category for zero or more business contacts. This is total freedom. However, as stated earlier, we have no way of representing this in a physical database, and we have no way of taking care of all intersections in the ER model either, so we dissolve the relationship like this:
We may now read all rows in contact role to find out which roles a given business contact has towards our business.
Do not be afraid to use this construction. In my experience, it will always add flexibility into the finished analysis result.
Next: Involuted (or recursive) relationships >>
More Development Cycles Articles
More By Alf A. Pedersen
/ 49 
