MySQL – Many to Many relationship, or one to many with additional columns


Our product contains some constraints that made us contemplate which table design will yield the best performance.

Our model:

  • Question table which contains fields like id, body, difficulty, and tags.
    • Each question can have up to five different tags.
  • Tags table which contains fields like id, and tag name.

The question table will have significantly more reads than writes.

We considered two design options:

  1. Many to many relations where we'll have the following tables
  • Question – TagsQuestion – Tags
  1. One to many relation where we'll have the following tables
  • Question – Tags

In this option, the question table will contain five tags columns (TagA, TagB, etc)

The "read" queries will mostly fetch questions by tag or tags.

Best Answer

Basically you can only use

Question - TagsQuestion - Tags

With the bridge table, if one tags is set for many questions.

It wuld also save some space, if you have a lot of questions.

The sencond option has the problem, that if you wqant all tags t be displayed you need five INNER JOINs, for every Tag one, while you can make the same query with two joins, with the first option..

Overall, the first option with bridge table, is the more flexible and space consuming option.