There are four types of relationships that can exist between two entities.
• One-to-Zero/One Relationship
• One-to-One Relationship
• One-to-Many or Many-to-One Relationship
• Many-to-Many Relationship
Such a relationship exists when each record of one table is related to Zero or one record of the other table.
For example, If there are two entities 'Employee' (Id, First_Name,Last_Name, Email,Mobile, Address_Id)and 'Address'(Id, Street,City,State,ZipCode). So, each Employee can have Zero or one Address.
Such a relationship exists when each record of one table is related to only one record of the other table.
For example, If there are two entities ‘Person' (Id, Name, Age, Address)and ‘AadhaarCard'(AadhaarCard'_Id, AadhaarCard'_No). So, each Person can have only one AadhaarCard and each AadhaarCard belongs to only one Person.
Such a relationship is not very common. However, such a relationship is used for security purposes. In the above example, we can easily store the AadhaarCard Number in the ‘Person’ table only. But, we make another table for the 'AadhaarCard' because the 'AadhaarCard' number may be sensitive data and it should be hidden from certain users. So, by making a separate table we provide extra security that only certain database users can see it.
One-to-Many or Many-to-One Relationship
Such a relationship exists when each record of one table can be related to one or more than one record of the other table. This relationship is the most common relationship found. A one-to-many relationship can also be said as a many-to-one relationship depending upon the way we view it.
For example, If there are two entity type ‘Employee' and ‘Department' then each ‘Department' can have more than one ‘Employee' but each ‘Employee' belongs to only one ‘Department'. In this example, we can say that each Department is associated with many Employee. So, it is a one-to-many relationship. But, if we see it the other way i.e many Employee is associated with one Department then we can say that it is a many-to-one relationship.
Such a relationship exists when each record of the first table can be related to one or more than one record of the second table and a single record of the second table can be related to one or more than one record of the first table. A many-to-many relationship can be seen as a two one-to-many relationship which is linked by a 'linking table' or 'associate table'. The linking table links two tables by having fields which are the primary key of the other two tables. We can understand this with the following example.
Example: If there are two entity type ‘Customer’ and ‘Product’ then each customer can buy more than one product and a product can be bought by many different customers.
Now, to understand the concept of the linking table here, we can have the ‘Order’ entity as a linking table that links the ‘Customer’ and ‘Product’ entity. We can break this many-to-many relationship into two one-to-many relationships. First, each ‘Customer’ can have many ‘Order’ whereas each ‘Order’ is related to only one ‘Customer’. Second, each ‘Order’ is related to only one Product whereas there can many orders for the same Product.
In the above concept of linking can be understood with the help of taking into consideration all the attributes of the entities 'Customer', 'Order' and 'Product'. We can see that the primary key of both 'Customer' and 'Product' entities are included in the linking table i.e 'Order' table. These keys act as foreign keys while referring to the respective table from the 'Order' table.
We can easily configure the relationship between tables with the code-first workflow. The Code-First provides two ways to configure relationships
1. Data Annotations and
2. Fluent API
In the next article we will learn to configure relationship between tables using data annotations