Let's say I need to store Apartments in my db. Apartments have several attributes like size, area, is it furnished or not, etc.
What is better: to store each value in a separate column or have several columns with arrays in them?
1
-
2First normal form of database design: each column is atomic. Therefore, I would not stuff a whole array into a single column. It gets messy and some day, sooner or later, that "design" will come back to haunt you. Don't do it.marc_s– marc_s2011-01-26 15:24:28 +00:00Commented Jan 26, 2011 at 15:24
Add a comment
|
4 Answers
I agree with Joel that it depends, esp. on how many columns. For what it's worth though, I'd always start the straightforward way, and do one column per 'interesting' attribute. Certainly anything you want to query on, or do math upon ($/sq-ft), is easiest in a column.
Maybe some blocks that are just 'display' can be grouped ... but I wouldn't necessarily start that way.
Comments
You can't answer this question with one singular answer. "it depends" on how many columns we're talking about, and if they can reasonably be decomposed (ie. normalized) into separate entities :-P
Comments
If you are talking about relational databases like Oracle, mysql, postgre, then generally it is best to have one column for each attribute. With this method you can easily search your database for a specific attribute.
Storing multiple values in one column is uncommon and only use for very special performance optimizations.
Have a look at database normalization: http://en.wikipedia.org/wiki/Database_normalization
Comments
I usually look at the data that I'll be storing and see if the data is something I'll need to search by later... For example... If I have an apartment table and I'll probably be searching for apartments with 4 bedrooms (or 1,2,3...) I'll try to put the bedroom count in a separate column... if the bedroom count in in a string of other parameters for the apartment, an index on "bedroom count" can't be used.
