0

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.

user1534664
  • 3,258
  • 8
  • 40
  • 66
  • 1
    `0:n` vs. `1:n` is important - is that FK mandatory or not? Also, it's important to know if something is a `1:n` or a `m:n` relationship - the first can be handled with a simple FK column, the second requires an intermediate "link" (or "bridge") table in the database. Cardinality has important repercussions! – marc_s Sep 07 '14 at 11:43
  • @marc_s Can you explain that a bit more in depth? Why is `0:n` vs `1:n` important? I understand that a `m:n` relationship evaluates to an intermediate table, so that does answer my question in a way :) – user1534664 Sep 07 '14 at 11:50
  • If you have a `1:n` relationship, it means that the FK column in the child table is **mandatory** - it **must have** a value - always (the column must be defined as `NOT NULL` in SQL "speak"). If it's `0:n`, the FK column could also be left empty (it's defined as `NULL`able in SQL) which means there's no relationship between that child entity, and any parent - that child entity can exist on its own, without a parent – marc_s Sep 07 '14 at 11:51
  • @marc_s I'm not very familiar with cardinality yet. If I understand you correctly, you can deduce whether a relationship is optional or mandatory based on whether its a `1:n`,`n:1`, `1:1` or `n:m` relationship? So a `1:n` relationship implies its a mandatory relationship? I'm going to try and look for more sources on this, my textbook is confusing me. – user1534664 Sep 07 '14 at 12:01
  • The linkage between mandatory vs optional foreign keys does not impact 1:n vs 1:1. It impacts 0,1:n vs 1:n. Does the child record need to have a parent record or can it be an orphan? This is extremely important for practical reasons of referential integrity. – Joel Brown Sep 07 '14 at 19:34
  • @marc_s I didn't know what a child or parent table was before. After doing some research I understand what you mean now. The rules you mentioned only apply when there's referential integrity (RI) though, right? If there's no RI I could just fill in any FK value even if the key does not exist in the parent table, right? – user1534664 Sep 08 '14 at 11:12

1 Answers1

4

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".

Community
  • 1
  • 1
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
  • I could quibble over the "authors" vs. "senders" concept related to e-mails, but I won't. Excellent answer. – Erwin Smout Sep 08 '14 at 07:54
  • Thank you, but as a beginner I'm still quite confused about the notation... I thought when you speak of a 1:n relationship you were just refering to the maximum cardinality between two tables, so not the optionality. – user1534664 Sep 08 '14 at 11:05