Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

My website has different content types. All content types should be tag-able (except if specified in the model, whatever). I can think of 2 options for my design:

2 tables option : 1 table for the tags (+one other, its i18n content) plus 1 table for the association (content_type , content_id, tag_id).

Pros :

  • Creation is faster
  • model parts for the tag is the same (so inherited model)
  • same thing for controlers.

Potential Cons :

  • Will the performance be poor?
  • will the table be too large soon ?
  • How to query for tags clouds (related to all grouped contents)?

many tables options : 1 table for tags, an association table per type of content.

Pros :

  • Tables will be shorter

Cons :

  • Have to customize each model or write a logic/footprint to query the right table
  • ugly long request / php code to join tags of all content types


Additional Facts :

  • Front side will have cache, tags are set by content writers.
  • There are 10 content types.
  • There won't often be new types of content.
  • Content count is ~500 , will ~double in 3 years.
  • MySQL engine InnoDB.
  • built with yii framework.
  • will query tags for a defined content type
  • will query tags for any type of content (together, one big tag cloud for example).

So my idea was to use the first design (2 tables). But I'm not a db expert ... Would you recommand the second option ? Why ?

share|improve this question

1 Answer 1

up vote 4 down vote accepted

I would go for option 1 (two tables).

If you only plan to have 1000 content items (and in 3 years, not now), I don't think you will have many performance problems unless each content item is tagged with a dozen tags where most are unique to that item. And even then I don't think you'll see much of a performance problems. It also simplifies your structure if you have 1 tag table for every content type and then you decide to add/remove/change content types later.

If you're really not sure, do you have the time/resources to build two sample DBs, one based on each structure, populate them with what you think is a realistic load (you can write a script/program to generate & insert test data, right? with "boring" tags and content such as "content_1..content_2000 and tag_1... tag_5000) and actually test which performs better?

share|improve this answer
    
That answer my question, thanks :) Also thanks to @DTest for editing my question –  7seb Jun 1 '12 at 9:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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