SQL Database Design » 5. Properties of Relationships » 5.6 Epilog on Relationship Notations

5.6 Epilog on Relationship Notations

In crow foot notation, optionalities are displayed as small circles, while the basic cardinalities are shown as single lines and three tree-like lines at the connection ends. The following shows the same relationships as before in crow foot notation:

Optionality with Cardinalities (Crow Foot Notation)
Optionality with Cardinalities (Crow Foot Notation)

The crow foot notation has three disadvantages compared to the Chen notation: first, it has no symbol for hierarchical relationships. Second, it isn't possible to connect attributes to relationships, as you usually do for many-to-many and/or higher-degree relationships. Even if you do connect attributes to the crow foot lines, it looks rather akward. As a third disadvantage, the crow foot notation doesn't allow you to specify concrete upper and lower bounds, like "2..5".

The two relationship notations clearly serve different purposes: while the Chen notation is more suitable for conceptual models, the crow foot notation should be preferred for logical and physical (database) designs. All hierarchical relationships are translated to a bundle of one-to-one sub-super relationships. Many-to-many and higher-degree referential relationships are translated to an extra join table, connecting the adjacent tables via one-to-many relationships. These facts eliminate the first two disadvantages of the crow foot notation in relational designs.

Because logical and physical models using the classic Chen notation quickly become cluttered with circles representing the attributes, the crow foot notation is better suited for logical and physical designs. Many design tool vendors seem to concurr, as virtually all tools offer the crow foot notation but not Chen.

References for Properties of Relationships:

Last updated: 2010-10-13