Understanding the tables cardinality concept

As a trainer, I am often asked the question how to set the Relation’s properties in a table’s relations node, and how / where are those used, so, this would not necessarily be a new topic but rather a review of the concept:

- a relation between two tables is the fundamental way of describing how we view the data in a relational database;
- a relation is actually a column (FK - foreign key) on the second table pointing to a column (PK - primary key) in the first table, field that identifies uniquely a record;
- one table can be seen as the parent table and the other one, that will hold the relation the child table;
- you would create the relation always on the child table;
- the cardinality concept simply talks about how many records from the table in discussion we can have pointing to one record in the other table; we always relate as we would have one record in the other table, and looking from its perspective to the other;
- the Cardinality property refers to the table you are creating the relation on, so that would be the child table; the values for the cardinality can be: ZeroOne, ExactlyOne, ZeroMore, OneMore.  So, while on any record in the child table you could set the related columns value to point to one record in the parent table, you could actually have more child records looking at the same parent record, so from here the options above;
- the RelatedTableCardinality property refers to the parent table; the values can be: ZeroOne, ExactlyOne. Let us explain them. So for every child, you could have a parent for it or maybe no parent. If the relation's column would be mandatory, that means that from a design point of view your parent would always be required, so that would make it an ExactlyOne. Now, this leads to the third property we have to set which is RelationshipType covered in the next post.

No comments:

Post a Comment