CODEWITHSUNDEEP
databasedatabase-design
Ghinzu
Ghinzu

Reputation: 39

Database design: many columns vs several with arrays in them

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?

Upvotes: 0

Views: 250

Answers (4)

NoradX
NoradX

Reputation: 11

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

Upvotes: 1

john personna
john personna

Reputation: 442

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.

Upvotes: 2

John K.
John K.

Reputation: 5474

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.

Upvotes: 0

Joel Martinez
Joel Martinez

Reputation: 47751

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

Upvotes: 1

Related Questions