How to implement tag system

user_207633 0
Viewed 33221

I was wondering what the best way is to implement a tag system, like the one used on SO. I was thinking of this but I can't come up with a good scalable solution.

I was thinking of having a basic 3 table solution: having a tags table, an articles tables and a tag_to_articles table.

Is this the best solution to this problem, or are there alternatives? Using this method the table would get extremely large in time, and for searching this is not too efficient I assume. On the other hand it is not that important that the query executes fast.

tagging system algorithm
7 Answers
CREATE TABLE Tags (
    tag VARHAR(...) NOT NULL,
    bid INT ... NOT NULL,
    PRIMARY KEY(tag, bid),
    INDEX(bid, tag)
)

Notes:

  • This is better than TOXI in that it does not go through an extra many:many table which makes optimization difficult.
  • Sure, my approach may be slightly more bulky (than TOXI) due to the redundant tags, but that is a small percentage of the whole database, and the performance improvements may be significant.
  • It is highly scalable.
  • It does not have (because it does not need) a surrogate AUTO_INCREMENT PK. Hence, it is better than Scuttle.
  • MySQLicious sucks because it cannot use an index (LIKE with leading wild card; false hits on substrings)
  • For MySQL, be sure to use ENGINE=InnoDB in order to get 'clustering' effects.

Related discussions (for MySQL):
many:many mapping table optimization
ordered lists

user_17668310
Related