Picking the right database design can be tricky even for seasoned software developers. Not to mention realizing the true differences between one-to-many and many-to-many relationships can be a real brain buster. This blog will teach you how and when to choose the right database design.
Database Relationship Types
- One-to-one – a record in one table is related to one record in another table. A good example of a common one-to-one relationship is when a user has an address. Instead of placing the address in the user table we create a separate table for addresses.
- One-to-many – a record in one table is related to many record in another table. A good example would be if users had multiple address. Instead of just having one address per record a user could have multiple address!
When To Choose Many-To-Many Over One-To-Many
- Many-to-many relationships – multiple records in one table are related to multiple records in another table.
Many-to-many relationships are slightly less common but still very important. Let’s say we are creating a database for a university with a student/class relationship
Rule #1: A student can be enrolled in multiple classes at a time.
Rule #2: A class can have many students
We can’t add the primary key of class to students, because this will only make a one-to-many!
| Id | ClassId | Name |
| 1 | 33, 67, 87 | Teddy Smith |
Just by looking at this table you can tell something is not right. Having multiple values in one column is a huge red flag that a database needs to be normalized/refactored.
So let’s try to add more columns!
| Id | ClassId1 | ClassId2 | ClassId3 | Name |
| 1 | 33 | 67 | 87 | Teddy Smith |
Once again, this table looks terrible. The best way to fix this is to make a many-to-many relationship via a bridge table.
Bridge Tables
A bridge table is a database table that sites between two other tables of a many-to-many relationship. The purpose of bridge tables is to store records of each combination of the two other tables.
Bridge tables typically have the name of the two tables joined together like this:
| StudentId | ClassId |
| 3 | 4 |
| Id | Name |
| 3 | Teddy Smith |
| Id | Name |
| Programming |
