CODEWITHSUNDEEP
mysqlsqlrdbmsjunction-table
tdavis
tdavis

Reputation: 11

Are there times when junction tables add more complexity than necessary?

Let's say you have a recipe app. Each recipe could have ingredients, directions, and tags. Each of those could be ManyToMany with recipe.

As well, directions could be ManyToMany with steps, and ingredients could be ManyToMany with amounts, and amounts could be ManyToMany with measurements.

Is there a convention for when it does and doesn't make sense to use a junction table?

Upvotes: 1

Views: 150

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562791

Yes, the convention is called database normalization.

Each many-to-many relationship requires its own junction table, if you want your database to be in normal form.

The purpose of normal forms is to reduce the chance for data anomalies. That is, accidentally inserting data that contradicts other data. If you follow rules of normalization, your database represents every fact only once, so there are no cases of data anomalies.

If you are uncomfortable with the level of complexity this represents, don't blame the tables! It's the real-world information you're trying to model that is causing the complexity.

Some people like to avoid the table, for example by storing a string in the recipes table with a comma-separated list of tags. This is an example of denormalization. In this example, it would make it simpler to query a recipe along with its tags. But it would make it harder to do some other types of queries:

  • How many / which recipes share a given tag?
  • What's the average number of tags per recipe?
  • Update all recipes to change the spelling of a given tag, or remove a given tag from all recipes that have it.

You may also like my answer: Is storing a delimited list in a database column really that bad?

In general every kind of optimization optimizes for one type of query, at the expense of other types of queries against the same data.

Organizing your data to conform to rules of normalization makes your database better able to serve a wider variety of queries.

Upvotes: 1

Related Questions