What is the purpose of data modeling cardinality?

Question

I understand what cardinality is, so please don't explain that ;-)

I would like to know, what the purpose of doing cardinality is in data modeling, and why i should care.

Example: In an ER model you make relations and ad the cardinality to the relations. When am i going to use the cardinality further in the development process? Why should i care about the cardinality?

How, when and where do i use the cardinalities after i finish an ER model for example.

Thanks :-)

Answer 1

Cardinalities tell you something important about table design. A 1:m relationship requires a foreign key column in the child table pointing back to the parent primary key column. A many-to-many relationship means a JOIN table with foreign keys pointing back to the two participants.

Answer 2

How, when and where do i use the cardinalities after i finish an ER model for example.

When physically creating the database, the direction, NULL-ability and number of FKs depends on the cardinalities on both endpoints of the relationship in the ER diagram. It may even "add" or "remove" some tables and keys.

For example:

• A "1:N" relationship is represented as a NOT NULL FK from the "N" table to "1" table. You cannot do it in the opposite direction and retain the same meaning.
• A "0..1:N" relationship is represented as a NULL-able FK from "N" to "0..1" table.
• A "1:1" relationship is represented by two NOT NULL FKs (that are also keys) forming a circular reference¹ or by merging two entities into a single physical table.
• A "0..1:1" relationship is represented by two FKs, one of which is NULL-able (also under keys).
• A "0..1:0..1" relationship is represented by two FKs, both NULL-able and under keys, or by a junction table with specially crafted keys.
• An "M:N" relationship requires an additional (so called "junction" or "link") table. A key of that table is a combination of migrated keys from child tables.

Not all cardinalities can be (easily) represented declaratively in the physical database, but fortunately those that can tend to be most useful...

---

¹ Which presents a chicken-and-egg problem when inserting new data, which is typically resolved by deferring constraint checking to the end of the transaction.