Menu
I've noticed that a lot of folks here cite tables with 20+ (I've seen as much as 55) columns in one table. Now I don't pretend to be a database design expert, but I've always heard that this is a horrible practice. When I see this, I usually suggest splitting into two tables with a one to one relationship: one containing the most frequently used data, the other with the least often used data. Though at the same time, there's the possible issue of performance (less JOINs and such). So my question is this:
When it comes to really LARGE scale databases, is there actually an advantage to having a large amount of columns, despite the fact that this usually leads to many NULL values?
Which is more of a performance hit: lots of columns with lots of NULLs, or fewer columns with lots of JOINs?
601
There isn't a number that's too many. That said, it sounds like, based on your description, that you're not dealing with a properly structured table. 180 columns to define a user and 280 columns to define a thing... That can't possibly be normalized or a fact table.
MySQL has hard limit of 4096 columns per table, but the effective maximum may be less for a given table.
In the NO-SQL world (cassandra/hbase for example) there are no constraints on the number of columns and it's actually considered a good practice to have many columns. This also comes from the way it is stored (no gaps).
In Access, you can define up to 255 fields in a table. If you create 255 fields and then delete 10, Access does not release the fields from the internal column count. Also, for every field whose properties you modify, Access creates a new field and does not release the original field from the internal column count.
The design of the table depends on the entity it needs to store. If all the data belongs together, then 50 columns (or even 100) might be the correct thing to do.
So long as the table is normalized, there is no rule of thumb regarding size, apart from database capabilities and the need to optimize.
83
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
