Take the 2-minute tour ×

which structure is better?


postid category1 category2 category3
2            a         b        d

3            a         c       null


post table


category_option table

category     option

category1      a
category2      b
category3      c
category4      d

option_post table

post option
2      a
2      b
2      d 
3      a
3      c

it seems buiding query for first structure is easier than second structure.

share|improve this question
it really depends on what are you trying to achieve? in general, the second fill be normalized view. so it may be better, and in some cases faster; In other cases, first one will be easier and faster; give some more details –  pomaxa Nov 10 '11 at 23:53
add comment

6 Answers

These two structures model different things. The first one rigidly allows only (up to) 3 categories (and differentiates between categories by position), while the second one can model any number of categories (which are not distinguished by position). Which one is better really depends on what you are trying to accomplish...

On purely technical level, the second one might require a JOIN for some queries where the first one could satisfy the query from the single (and only) table. Whether this is a problem or not, again, depends on circumstances...

share|improve this answer
when u say join table you mean something like select * from table as t1,table as t2 and t1.option=a and t2.option=b and t1.postid=2 and t1.postid=t2.postid ? this is a optimize query? is there a better one solution? –  a.v Nov 11 '11 at 0:12
Yup, something like that, though usually a more modern syntax is used (Google for the JOIN keyword). A JOIN can be very quick or very slow depending on many factors, not in the least indexes. I warmly recommend reading use-the-index-luke.com for performance ramifications of indexes in general and JOINs in particular. –  Branko Dimitrijevic Nov 11 '11 at 1:36
add comment

Depends on requirements...

Do you anticipate increasing the number of options over time?

your first option is by far easier to code, the second option is much more modular design and scalable.

share|improve this answer
sometimes violating normal form for the sake of performance is worth it especially if you know this is just a temporary situation, a short term project or going to NEVER grow in size. But option 2 is much more "Book and theory" and the "right" way to do it. –  xQbert Nov 11 '11 at 0:13
add comment

It depends greatly on the nature of the categories. If the list is fixed and unlikely to grow, then the first structure works just fine and can be easier to work with. If the list of categories is likely to grow, then the second option will grow better.

It also matters if the category values are sparse. If most of the categories will not have values, then the second approach will take up much less space. If every item will have values in every category, this is not an issue.

It is important in this case to understand what "likely" means. It doesn't mean that you the designer don't think it will grow. It means that the list of categories is well-understood and mature, and so unlikely to grow. I kept looking for examples, but none come to mind.

There are good reasons to select the first, but do so with care - switching to the second option in a production system will be a nightmare.

share|improve this answer
add comment

The second one is better. The first is a violation of First Normal Form:


share|improve this answer
add comment

2nd one better. it's typical many2many with join table case.

if you do it in 1st way, what are you gonna do if there are new category category 4,5,6,7,8... come? add new columns to the table?

And, I don't know if you have a requirement like, "how many posts with category option 'c'"? 2nd one is easy to do the statistic, but the 1st one...

share|improve this answer
Far too simple an answer. There are plenty of good places to use a flat table structure. –  drdwilcox Nov 10 '11 at 23:57
I agree - this is NOT the correct answer. It is highly situational. –  skaz Nov 11 '11 at 0:07
add comment

category_option must have category id to make a conjunction table with opton_post otherwise there is no mean to create that 2nd table structure.

two thing you can achieve here with that structure.

1) make a one to one relationship with post to category. (this mean you can add more categories in future if its needed)

2) in this structure null values automatically avoided. this mean no more handling null values in table or in sql queries.

hope this helps.

share|improve this answer
add comment

Your Answer


