CODEWITHSUNDEEP
mysql
phistakis
phistakis

Reputation: 221

mysql design: which is better, many columns or many rows?

I need to design a new table for my project, which basically holds a list of 41 numbers per key. The first number in the list is some distance measurements, while the rest of the numbers represent weighted bins of percentages as such:

which all sum up to 1*10 + 2*10 + 3*10 + 4*10 = 100%

The normal usage of this data would be summing the bins vertically. (i.e. key1-bin1 + key2-bin1 + .. ; key1-bin2 + key2-bin2 + .. ; etc. )

Now, assuming this will never change and there will always be 40 bins with the same bin dispersion, which is better?:

or

The obvious advantage of the first option is the reduction of data duplication, plus I really don't like my "-1" workaround.

But this feels really dirty and extremely "hard-coded", and I remember being taught that databases somehow "prefer" the second option and are designed to handle it better than the first in terms of compression, indexing etc. (Plus the queries will probably look nicer in the second option, but I can live with ugly queries in my code)

Thanks!

Upvotes: 3

Views: 110

Answers (1)

O. Jones
O. Jones

Reputation: 108851

The key to your choice of answer is "assuming this will never change." If that is true, then you should go with the many-column table. You're right that MySQL and other dbms systems handle normalized data (your first option) pretty well. But if I understand your problem correctly, you will always have the 41 data points for each observation. That means your first option will always have 41 rows per observation.

There's no magic in MySQL or other DBMSs. They just grind data. Sure, they do it pretty efficiently. But forty-one times as many rows as observations is a lot.

If you sometimes had one, sometimes three, and sometimes ten data points per observation, you'd choose the normalized, short-row, option.

Upvotes: 3

Related Questions