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

Question

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

2. 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.

Answer 1

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.

Answer 2

Are the "tags" essentially true/false? Are they pre-defined? (That is, you are not likely to add new flags in the future.) If so, a compact way to store them is via

CREATE TABLE ...
    tags SET('approved', 'sold', 'built', 'answered', 'lost') NOT NULL

There are various functions for testing flag(s), especially FIND_IN_SET().

(It would help if you gave us some likely tag names.)

Or not Many-to-Many

The last time I implemented a "tags" feature, I had (using your terminology):

CREATE TABLE tags (
    question_id MEDIUMINT UNSIGNED NOT NULL,
    tag  VARCHAR(55) NOT NULL,
    PRIMARY KEY(question_id, tag),
    INDEX(tag, question_id)
) ENGINE=InnoDB;

The PK made it performant to find the tags for a given question; the secondary index made it efficient to find which questions have a particular tag. This scales nicely and is simpler than the 3-table approach. An unlimited number of tags is possible; the drawback is that spelling errors are not caught.