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
One-to-Zero/One Relationship
In one to Zero type of Relationship a record in a table can be associated with either one record of another table or it may have no related record in 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.
One-to-One Relationship
In one to one relationship each record of a table can be associated with one and only one record in another 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 that common as other relationships but is often 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 is very sensitive data and it should be hidden from certain users. So, by making a separate table for AadharCard we provide extra security such that only the authorized user can see it.
One-to-Many or Many-to-One Relationship
In one-to-many relationship a record in a table can be associated with one or more records in another table. It completely depends upon the way how do one analyze the relationship, if we will look from the opposite direction then one-to-many relationship will become the many-to-one relationship. .
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.
Many-to-Many Relationship
In Many-to-many relationship one or more records of one table can be associated with one or more records or another table, it can also be seen as a two one-to-many relationship which is linked by a 'bridge table'. The bridge table contains the primary keys of both of the table. We can understand this with the following example.
Example: Suppose there are two entities Customer and Product, then each custome can buy more than one product and a product can also be bought by more than one customer, so here we need to create a associate table or bridge table which will keep the information that which customer has bought which product.
Now, to understand the concept of the linking table or bridge table, we can have the ‘Order’ entity as the linking table that links the ‘Customer’ and ‘Product’ entity. We can break this many-to-many relationship into two one-to-many relationships such that each customer can have many products and each product can be associated with many cistomers.
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