0

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.

0

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.

0

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.

6
  • Hi, tags are technology names, for example: java8, c#7, python7 etc. With time new tags are added to the table.
    – Uri Lukach
    Jan 18 at 5:47
  • Then you might prefer to use bits in an UNSIGNED INT -- 32 'tags' would be possible.
    – Rick James
    Jan 18 at 7:53
  • Hi Rick, so in the case that we will add many more tags in the future should we use many to many relationships? with connecting table QuestionsTags? or is there a better way to model this?
    – Uri Lukach
    Jan 21 at 9:49
  • @UriLukach - Which way? It depends. Many-to-many is good when there is a lot of info (think "columns") on both sides of the relationship. "Tags" is a term that usually implies just a word, nothing else. So, do you need to have a table with some columns of info on "python7" or not?
    – Rick James
    Jan 21 at 18:22
  • Hi Rick, our Tags table should be able to grow with time, and eventually contains very few columns. I am not sure we have a way to bypass many to many relationship
    – Uri Lukach
    Jan 22 at 15:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.