Columns or rows to store related properties
I want to track a variety of properties relating to a row in a topics table, but I'm not sure if I should create a column in the topics table or create a properties table and insert each property in it's own row there. (or maybe some third option I'm not thinking of?)
For example:
properties
----------------
prop_id
type (representing boolean,range,text, or a date)
title
The two ways I can think to reference this in the main table are:
topic
----------------
topic_id
title
prop_id1
prop_id2
prop_id3
....
or make a separate table for the data with one property on each row:
properties_data
----------------
topic_id
prop_id
bool
range_min
range_max
text
date
It seems to me the first method would be more effective, but I expect to have ~30-40 properties that might or might not be in use for each topic, so I'm not sure which to use.
Is there a standard method of accom开发者_开发百科plishing this task, with different types of data that all relate (and in theory would be looked up at the same time) to the main table row?
If your going to have a variable number of properties attached to each topic, then you should go with the later option you have identified above. That will give you maximum flexibility, and if you add the proper indexes to your database, the performance hit should be fairly negligible.
精彩评论