I was asked this question in an interview. How do you implement one-to-one, one-to-many, and many-to-many relationships while designing tables
- one-one relationship is most likely forced by business rules but it does not flow naturally from the data. One can combine both tables into one table without breaking normalization rules. To implement this relation, a primary key is used to uniquely identify a record in the first table, another primary key for the second table is used as a foreign key in the first table to link the two tables together.
- one-m relationship is like parent to children relation where the primary key table contains only one record that relates to none, one, or many records in the related table. To implement this relation, a primary key from the first table is used as a foreign key in multiple records in the second table
- many-many relationships require a third table known as the linking table because relational databases can not directly accommodate that.