CODEWITHSUNDEEP
database-design
user1534664
user1534664

Reputation: 3418

Why do we define the minimum and maximum cardinality? - ERD

Why do we define the minimum and maximum cardinality? What is it used for? Readability? How does it relate to the concept underneath? If I understand correctly, a relationship between entity's is nothing more than a SQL join.

Upvotes: 0

Views: 3582

Answers (1)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52137

Concept

Nope, relationship is not a JOIN, although JOINs are often (but not always) done on top of relationships.

Cardinality is fundamental property of relationships between different pieces of data you are trying to represent in your database. For example, let's consider a "books and authors" database...

  • One author can have multiple books, and one book can be written by multiple authors. In other words, there is an M:N (see Notation section below) relationship between books and authors.
  • On the other hand, one e-mail cannot belong to multiple authors, so this is a 1:N relationship between authors and e-mails (in that order).

The important point is that these relationships need to be enforced no matter how you query or attempt to modify the data. Letting the DBMS enforce them for you by through declarative referential integrity (i.e. foreign keys) is usually the best way of doing it.

Notation

When you have an Lmin..Lmax : Rmin..Rmax relationship1, it means that:

  • for any given row in the left table, there can be at least Rmin and at most Rmax rows in the right table,
  • and for any given row in the right table, there can be at least Lmin rows and at most Lmax rows in the left table.

When Lmax = 1, omitting Lmin means the lower bound is 0 or 1 (ditto for Rmax/Rmin). Confusingly, saying 1:N can mean either 0..1:N or 1..1:N.

When Lmax = N, omitting Lmin typically implies 0 (ditto for Rmax/Rmin). Non-zero lower bounds are extremely rarely used on the "many" side of relationships.

Replacing Lx and Rx with "0", "1" and "N" will give you various possible combinations, the most common and important of which are:

  • 0..1 : 0..many (aka. zero or one to many or 0..1:N), and is modeled by a NULL-able foreign key within right table, referencing left table. Left and right tables are often called "parent" and "child", in this scenario.
  • 1..1 : 0..many (aka. one to many or 1..1:N), essentially same as the above, except the foreign key is NOT NULL.
  • 0..many : 0..many (aka. many to many or M:N), which is modeled by so called "junction" or "link" table that sits between left and right table.

Remarks

The term "cardinality" has another (and quite distinct) meaning, which is related to the number of rows returned from a (sub)query. Unless clear from the context, always clarify which of the two you are using...

Also, there is a difference between relationSHIP and relatiON. "Relational" databases happen to derive their name from the latter.

1 Pronounced "Lmin or Lmax to Rmin or Rmax".

Upvotes: 4

Related Questions