Our product contains some constraints that made us contemplate which table design will yield the best performance.
- 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:
- Many to many relations where we'll have the following tables
- Question - TagsQuestion - Tags
- 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.