How many columns of a database table are too many?

Question

I took part in the development of the project, which has a user table with more than 30 columns. And the bad news is that changes and additions to the columns continue to occur.

It is not right.

Do I have to click to have additional fields move into the second table as values ​​and create a third table that stores the column names?

user id email user_field id name user_value id user_field_id user_id value 

Answer 1

Do not use key / value route. SQL is not designed to process it, and it will force the actual data from your database to perform self-sacrifice. (Examples: indexes do not work well. Joins are very fun when you need to join to get the data you are joining. This continues.)

As long as the data normalizes to a decent level, you do not have too many columns.

EDIT: To be clear, there are some problems that can only be solved with the key / value route. "Too many columns" is not one of them.

Answer 2

It is hard to say how many are too many. This is really very subjective. I think the question you should ask is not: “Are there too many columns?”, But rather, “Are these columns worth it?” I mean, if there are columns in your user table that are not necessarily user properties, then they may not belong. For example, if you have a bunch of columns that summarize the user’s address, then you might pull them into the address table from FK to User.

I would avoid using key / value tables if possible. It might seem like a simple way to make things extensible, but it's really just a pain in the long run. If you find that your schema changes very sequentially, you may want to install some kind of change control in place to check only those that are needed, or switch to another technology that better supports the absence of a schema without storage, for example NoSQL with MongoDB or CouchDB.

Answer 3

This is often referred to as EAV, and whether it is right for your database depends on many factors:

http://en.wikipedia.org/wiki/Entity-attribute-value_model

http://karwin.blogspot.com/2009/05/eav-fail.html

http://www.slideshare.net/billkarwin/sql-antipatterns-strike-back

Too many columns are actually not one of them.

Answer 4

Changes and additions to the table are not bad, if this means that they accurately reflect the changes in your business requirements.

Answer 5

If changes and additions are permanent, you may need to sit down and better define the requirements. Now I can’t say that 30 columns are toomany, because it depends on how wide they are and whether it is something that needs to be transferred to the linked table. For instnce, if you have fields like phone1, phone2, phone 3, you have a mess that needs to be split into a linked table for user_phone. Or, if all your columns are wide (and the overall width of the table is wider than the pages on which the data is stored in the databases), and some of them are not so often needed for your queries, they may be better in a related table, one relation. I would probably not do this if you have no performance problem.

However, of all the possible options, the EAV model you described is the worst both in terms of performance and in terms of performance. It is very difficult to write decent queries for this model.

Answer 6

It really depends on what you are trying to do.