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

database-designerdjoin;MySQL

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.

Question - TagsQuestion - Tags