How many database table columns are too many?

Question

I've taken over development on a project that has a user table with over 30 columns. And the bad thing is that changes and additions to the columns keep happening.

This isn't right.

Should I push to have the extra fields moved into a second table as values and create a third table that stores those column names?

user
    id
    email

user_field
    id
    name

user_value
    id
    user_field_id
    user_id
    value

Answer 1

Do not go the key / value route. SQL isn't designed to handle it and it'll make getting actual data out of your database an exercise in self torture. (Examples: Indexes don't work well. Joins are lots of fun when you have to join just to get the data you're joining on. It goes on.)

As long as the data is normalized to a decent level you don't 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" isn't one of them.

Answer 2

It's hard to say how many is too many. It's really very subjective. I think the question you should be asking is not, "Are there too many columns?", but, rather, "Do these columns belong here?" What I mean by that is if there are columns in your User table that aren't necessarily properties of the user, then they may not belong. For example, if you've got a bunch of columns that sum up the user's address, then maybe you pull those out into an Address table with an FK into User.

I would avoid using key/value tables if possible. It may seem like an easy way to make things extensible, but it's really just a pain in the long run. If you find that your schema is changing very consistently you may want to consider putting some kind of change control in place to vet changes to only those that are necessary, or move to another technology that better supports schema-less storage like NoSQL with MongoDB or CouchDB.