Core principle
The name can be a bit intimidating. But all it’s about is that there are different types of relationships between instances of table A and table B in a schema of a relational database.
So let’s expand a bit on that thereby focusing on the level of tables and forgetting the rest for a second. We need to look on the row level or the instances of a table. The critical question is how many instances of table B are associated with ONE instance of table A.
This may sound a bit abtract and theoretic or even irrevelant. But it is very much relavant when combining two or more tables. This is when instances of different tables get associated with each other. The type of cardinality determines how big the resulting table will be after the join. If a join goes badly the result can easily become unmanagable. This only one reason but in itself it is important enough that cardinalities need be understood.
Overview: 3 Different scenarios
There can be of course more than 3 cardinalities but I will simplify to only 3 different scenarios:
- when one instance of table A is matched by one instance of table B
- when one instance of table A is matched by more than one instance of table B and
- when more instances of table A are matched by more than one instances of table B.
We can name the first scenario a ONE-TO-ONE relationship. The other two are called ONE-TO-MANY and MANY-TO-MANY accordingly. Which type of cardinality is present also depends on the viewpoint or concrete, whether we look from table A or table B!
Example: ONE-TO-MANY
I’d like to illustrate a ONE-TO-MANY relationship by a fictive scenario. A classic example would be at a university. Imagine the administration has a database and two tables are about Departments and Professors.
Table A: Departments
Table B: Professors
Both tables will contain many different instances but the sheer number of instances is not decisive. The relationship between the instances of each table by contrast is.
In simple scenario each Professor is associated to exactly one department. So we could tend to think one professor and one department results in a ONE-TO-ONE relationship. But this view is not true and misleading because each department has more than one associated Professor. So we have ONE department out of Table A and MANY (more than one) Professors for each Department out of Table B. You might have guessed already that the relationship and therefore the Cardinality is ONE-TO-MANY here.
Conclusion
This abstract view might take some time to getting used to it. But again it is necessary because you need to know the cardinality when planning Joins between different tables and especially before you conduct them. The cardinality tells you which resulting table size you have to expect and how computationally expensive it will be.