One To One
One record from the first table can be associated with only one record from the second table.
Example: Employee table and Address table
Ultimately, we merge the table together.
One To Many
one record from the first table can be associated with one or more records from the second table.
Example: Employee and department tables have one to many relationships.
To create one to many relationships: the rule is to take the id of the one side as a foreign key to the many side.
In other words, take the id of the department and insert in to the employee side.
More Example:
Merchant vs customers
patient and doctors (one patient can see only one doctor but a doctor can have one or more patients)
student and advisors
Many to Many
One or more records from the first table can be associated with one or more records from the second table.
Examples:
- student vs instructor
- student vs course
- patients and doctors (one patient can see one or more doctors)
- customer and products, they purchase.
- Merchant vs products
(One customer can purchase several products and, one product can be purchase by many customers)
(One merchant can sell several products and, one product can be sold by several merchants)
To create many to many relationship: the rule is to take the primary keys of both tables in to a separate table. We have to create a new (third) table to do this.