CODEWITHSUNDEEP
database-design
Jane Panda
Jane Panda

Reputation: 1671

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 accomplishing 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?

Upvotes: 1

Views: 317

Answers (1)

Dan Morphis
Dan Morphis

Reputation: 1727

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.

Upvotes: 2

Related Questions