Database design: many columns vs several with arrays in them

Let's say I need to store Apartments in my db. Apartments have several attribu开发者_StackOverflowtes 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?

---

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.

---

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

---

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

---

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.