E-R Diagram: 5 Mistakes to Avoid

• Good E-R diagrams capture core components of an enterprise.
• 5 things not to include in your diagram.
• Tips to guide you in your E-R diagram design.

In my previous blog post, I introduced you to the basics of E-R diagram creation. In this week’s post, I show you how not to make one. In addition to creating a clean, readable diagram, you should avoid any of the following poor practices:

1. Saying the same things twice with redundant representations.
2. Using arrows as connectors (unless it’s indicating a cardinality of “one”). 
3. Overusing composite, multivalued, and derived attributes.
4. Including weak entity sets when a strong one will suffice.
5. Connecting relationships to each other.

1. Don’t include redundant representations.

Redundancy is when you say the same thing twice. As well as wasting space and creating clutter, it also encourages inconsistency. For example, the following diagram states the manufacturer of “wine” twice: once as a related entity and once as an attribute.

If you include two (or more) instances of the same fact like this, it could create problems. For example, you may need to change the manufacturer in the future. If you forget to change both instances, you’ve just created a problem. Even if you remember to change both, who’s to say you (or someone else) didn’t add a third or fourth? Stick to one representation per fact and avoid trouble down the road.

2. Don’t Use Arrows as Connectors

Arrows have a very specific meaning in E-R diagrams: they indicate cardinality constraints. Specifically, a directed line (†’) indicates “one” with an undirected line (-) signifies “many” [2]. The following E-R diagram (C) shows an example of when you should use an arrow. A customer has a maximum of one loan via the relationship borrower. In addition, each loan is associated with a single customer via the same borrower relationship. Diagram (D) on the other hand shows that the customer may have several loans and each loan may be associated with multiple borrowers.

It’s possible to place more than one arrow from ternary (or greater) relationships. However, as these can be interpreted in multiple ways, it’s best to stick to one arrow. 

3. Don’t Overuse Composite, Multivalued, and Derived Attributes

Although you have many different elements to choose from in a diagram, that doesn’t mean you should use all of them. In general, try to avoid composite, multivalued and derived attributes [2]. These will quickly clutter up your diagram. Consider the following two E-R diagrams.

The first (A) shows some basic customer information. Diagram (B) shows the same information with the addition of many unnecessary elements. For example, although it’s possible to derive AGE from DATE OF BIRTH, it may not be a necessity to include it in the diagram. 

4. Limit use of weak entity sets

A weak entity set can’t be identified by the values of their attributes: they depend on another entity for identification. Instead of a unique identifier or primary key, you have to follow one or more many-to-one relationships, using the keys from the related entries as an identifier. It’s a common mistake for beginning database designers to make all entity sets weak, supported by all other linked entity sets. In the real world, unique ID’s are normally created for entity sets (e.g. loan numbers, driver license numbers, social security numbers) [2]. 

Sometimes an entity might need a little “help” with unique identification.  You should look for ways to create unique identifiers. For example, a dorm room is a weak entity because it requires the dormitory information as part of its identity. However, you can turn this weak entity into a strong once by uniquely identifying each room with its name and location [3].

Before you even consider using an entity set, double check to make sure you really need one. If an attribute will work just as well, use that instead [1].

4. Don’t connect relationship sets

Connecting relationship sets may make sense to you, but don’t do it. It isn’t standard practice. Connecting one relationship set to another is much like using a diamond to represent an entity. You might know what it means, but no one else will. Take this rather messy example of a wine manufacturer.

The “bought by” ” sold by” and “manfby” relationships are all connected. It could be that manufacturers buy their own wine back from themselves. Or, perhaps, sometimes manufacturers sell their own product. Whatever relationship is going on here, it’s confused and muddled by the multiple relationships.

Unless you want a meeting with your boss to explain what exactly your diagram means, leave creativity to the abstract artists and stick with standard practices.

References:

Images: By Author

[1] Chapter 2: Entity-Relationship Diagram

[2] Entity-Relationship Model.

[3] Admin: Modeling.