Search code examples
databaseerdcardinalityentity-relationship

ERD relationship cardinality - Is it better with reverse placement of symbols?

Contrary to traditional convention, could it be better to draw relationship cardinalities attached to the table having the relationships rather than the tables the relationships are with? In other words let the symbols show the nature of the following relationship line, rather than explaining the relationship line already followed.

I am making an entity relationship diagram for some tables in an SQL database. The use case for the ERD is when creating a new entry to a table in the database. The goal is to make it easy to understand which other tables the entry is depending on, thus requiring entries too.

The traditional convention is to draw lines from a table to related tables, and then where the lines' are connected to the other tables we draw the relationship cardinality symbols. So if an entry in Table R necessarily must have exactly one related entry in Table C it could look as follows: (Simplified diagram.)

Traditional diagram

To find which other tables a table is dependent on, it is necessary to follow every connection to a related table to determine required dependency. This can become cumbersome when a database has many tables.

Is it possible then to make an ERD where it is easier to locate dependencies? As stated in the question up top, the most logical solution seems to be to clearly indicate for the reader that the traditional convention for symbol placements have been reversed, and then let the relationship cardinality symbols show the nature of the relationship following the symbols. So if an entry in Table R necessarily must have exactly one related entry in Table C the more readable indication could look as follows:

Reversed diagram

Disregarding the fact that most people are used to the traditional variant, are there any other benefits to the traditional one when compared to the reversed one I propose directly above? I asked a professor once if my suggestion wasn't better because it allows for easily determining whether or not more tables were required for an entry, and in general allows a more intuitive reading, but he was not able to argue anything beyond what people currently are used to, and tradition is not always worthwhile.

Are there any other alternatives than the ones I have shown above that works better for my goal when having very many tables in the diagram?

Solution

  • On Wikipedia you will find a very interesting diagram comparing the way to represent cardinality in 6 different notations. There is only one which shows cardinality in the reverse way like you'd want it: it's the "ISO (min,max)" notation (see also this SO question). If you want to use it, stick to the corresponding notation.

    Do not reverse the use of Everest's crowfoot notation. You'd end-up being the only one understanding yourself, and maybe not even everyday if you have to switch between your practice and the common practice very often. So an advice: accept the current notations as they are: they have proven themselves in millions of projects around the globe.

    If most notations take the cardinality on side of the target, and if the only notation that put on the reverse side is almost dead (despite the support of international standardization), there must be advantages and reasons that explain this situation. One of the reason is probably that you much more often ask yourself, what is this entity/class related with (promoting the target consideration) rather than looking for dependencies (where the source view could have some benefits).

    Edit:

    I have looked up in my software-paleontology books, and have found a notation that uses your logic with a similar rationale.

    It was part of MERISE, a development methodology that was very popular in France in the 80s and 90s. It used a variant of the ERD notation, which was called MCD (Conceptual Data Model). It was later popularized by the PowerDesigner tool. It uses the min max notation without parenthesis, but uses a distinct way to represent relations:

    enter image description here

    Note that the relation symbol can be compressed into a small circle and the connection lines can be annotated. (More explanation, here, but unfortunately only in French - and UML seems to have killed that notation).