CODEWITHSUNDEEP
mysqldatabase-design
Dennis
Dennis

Reputation: 21

How many columns in table to keep? - MySQL

I am stuck between row vs columns table design for storing some items but the decision is which table is easier to manage and if columns then how many columns are best to have? For example I have object meta data, ideally there are 45 pieces of information (after being normalized) on the same level that i need to store per object. So is 45 columns in a heavry read/write table good? Can it work flawless in a real world situation of heavy concurrent read/writes?

Upvotes: 2

Views: 198

Answers (4)

allenskd
allenskd

Reputation: 1805

I don't know if I'm correct but I once read in MySQL to keep your table with minimum columns IF POSSIBLE, (read: http://dev.mysql.com/doc/refman/5.0/en/data-size.html ), do NOTE: this is if you are using MySQL, I don't know if their concept applies to other DBMS like oracle, firebird, posgresql, etc.

You could take a look at your table with 45 column and analyze what you truly need and leave the optional fields into other table.

Hope it helps, good luck

Upvotes: 0

ChrisLively
ChrisLively

Reputation: 88082

Taking the "easier to manage" part of the question:

If the property names you are collecting do not change, then columns is just fine. Even if it's sparsely populated, disk space is cheap.

However, if you have up to 45 properties per item (row) but those properties might be radically different from one element to another then using rows is better.

For example taking a product catalog. One product might have color, weight, and height. Another might have a number of buttons or handles. These are obviously radically different properties. Further this type of data suggests that new properties will be added that might only be related to a particular set of products. In this case, rows is much better.

Another option is to go NoSql and utilize a document based database server. This would allow you to set the named "columns" on a per item basis.

All of that said, management of rows will be done by the application. This will require some advanced DB skills. Management of columns will be done by the developer at design time; which is usually easier for most people to get their minds around.

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425753

If all or most of your columns are filled with data and this number is fixed, then just use 45 fields. It's nothing inherently bad with 45 columns.

If all conditions are met:

  • You have a possibility of the the attributes which are neither known nor can be predicted at design time

  • The attributes are only occasionally filled (say, 10 or less per entity)

  • There are many possible attributes (hundreds or more)

  • No attribute is filled for most entities

then you have a such called sparce matrix. This (and only this) model can be better represented with an EAV table.

Upvotes: 3

Dejan Marjanović
Dejan Marjanović

Reputation: 19380

"There is a hard limit of 4096 columns per table", it should be just fine.

Upvotes: 0

Related Questions