Relationships and Cardinality

In real world, entities have relationships with other entities.  Relationships define how two entities are associated with each other. The data modeling captures the nature of relationship between two entities. For example, the entity 'Customer' buys the 'Product' and the entity 'Vendor' sells the 'Product'. It captures the rules between these two entities. If two entities are Person and Telephone Number, the relationship may capture the rule "Each Person may have many Telephone Numbers" and "Each Telephone Number must belongs to one Person." 

Data rules are instructions on how data relate to one another.  There are two types of data rules, Cardinality Rules and Referential Integerity (RI) data rules.

Cardinality rules define the quantity of each entity instances that can participate in a relationship. For example: 

• Each Person can have one or many Phone Numbers.
• Each User ID must belongs to one User.
• Each Bus can contain many Seats.

Referential Integrity (RI) rules focus on ensuring valid values. For example:

• A Customer cannot exist without a valid Email Address.
• An Order cannot exist without a valid Product.
• A Shipment cannot exist without a valid Postal Address

Action Rules are instructions on what to do when data fields contain certain values. For example:

• New Users can download at most 5 Articles a year for free.

In data models, we can represents the data and enforce data rules (Quantity and Valid relationships), but we cannot enforce action rules on a data model. A online magazine data model can capture User Status such as New User or Existing User, as well as the number of Articles each User reading downloading each year, but cannot enforce that a New User download no more than 5 Articles.

Relationship Cardinality

As we already discussed cardinality defines the quantity of each entity instances that can participate in a relationship. Cardinality represented by symbols that appears on both end of the relationship line. Cardinality used to specify data rules and to enforce it. The domain values to choose from to represent cardinality on relationship is limited to three values: zero, one or many. In data modeling, the small line means "one". The circle means "zero" or "optional". The triangle with a line through the middle means "Many", this symbol also known as a crow's foot. Relationship lines are labeled to clarify the relationship and express the rule that the relationship represents.

Recursion:

A recursive relationship is a rule that exist between instance Relationships and Cardinalities of the same entity. A one-to-many recursive relationship describes a hierarchy, whereas a many-to-many relationship describes a network.

Sub-typing:

Sub-typing groups the common data fields and relationships of entities, while retaining what is unique within each entity. The sub-typing relationships implies that all of properties from the super-type are inherited by the subtype.

This post gives you an idea about relationships and cardinalities in data modeling. Next post let us discuss about the keys in data modeling.