databases: one-to-many becomes many-to-many

I’ve been thinking a lot about evolving data models recently. One problem that seems common is when a relationship that was assumed to be one-to-many becomes many-to-many.

This is probably more likely than we often think.

So, how would you solve this? Let’s look at an example and consider some options.

We’ll start with a simple example of classes and students.

One to Many

To begin, we’ll assume that a student can only enroll in a single class, but classes can have multiple students:

Unfortunately, this likely isn’t sustainable for most schools. Students will want to be able to enroll in multiple classes simultaenously so that they can graduate on time!

Many to Many (Initial)

Now that we know students want to be able to enroll in multiple classes, the first way we might think about doing that is storing which classes they’re enrolled in on the student table and which students are enrolled on the classes table.

This would allow us to look up all of a student’s classes and all of the class’s students.

This absolutely works, but there are some limitations here. For one, where would you store data about a student’s enrollment?

What’s an alternative? Join tables. Let’s look at those now.

Many to Many With Join Tables

One way that we can solve this is by reexamining our data model and converting our many-to-many relationships into multiple one-to-many.

Now we have a clean relationship between a student and a class and a natural home for information about that enrollment (e.g., grades, attendance record, etc.).

Wrapping Up

The point of this is not to say that there’s never a good time for a many-to-many relationship and/or that you need to refactor every example into one with a join table, but rather to call it out as a viable option, and certainly a tool to consider when designing the system up front.

After all, these kinds of conversions just become more and more expensive as the database fills up with existing data.

The goal is to think far enough ahead into potential use cases for the data that relationships that will evolve from one-to-one / one-to-many into many-to-many are accounted for early and pain is avoided.