How many fields is 'too many' in a table?

Question

I have a coworker who is planning a database for a new app that will have several tables with over 30 fields each. Is this excessive? Maybe I'm just not enterprisey enough to understand.

Edit: Also, a lot of the fields are option-type sort of things (like on a request form, would you like your widget to be yellow or green, he has a field for 'colour' with an enum). It's quite likely that these will be added to or removed over time. I haven't really done database design and try to stay away from it myself, so maybe I'm being completely stupid, but surely there's a better way of doing this??

Answer 1

there is no arbitrary limit; enough to get the job done is a good rule of thumb

if you have a better db design, suggest it

if you want more detailed feedback, post the schema

Answer 2

Of course, the standard answer is it depends. A table with that many fields could actually make quite a lot of sense in some situations.

Think about the data you'll be storing in there. Is it likely that many of these fields will be NULL? What's the likelihood that these fields change (eg: more are added)?

If only certain fields apply to certain objects, perhaps think about putting those fields into another table. Alternatively, store just the basic, common fields in one table, and the extra information in another table, one row per field. As I suggested for a different question (which might be helpful to you):

refs (id, title, refType)
-- title of the reference, and what type of reference it is

fieldDef (id, fieldName, refType, dataType)
-- name of the field, which reference types it applies to, and
-- what type of data is stored in these fields (ISDN number, date, etc)

fields (refId, fieldId, value)
-- where you actually add data to the references.

Note that this was downvoted, and probably with good reason. This is an option, not necessarily the best option, but it's still a workable method. The highest voted answer in the question I linked to there might be the best solution however.

---

Edit: since you say that it will be holding things like per-user settings (eg: widget colour), I'd actually recommend the method outlined above (with the three tables). Chances are that most people will leave things at the default, so you'll have a stack of useless information being stored. Please do read my answer in the other question because other readers have pointed out the shortcomings of this method.

Answer 3

The number of fields is usually not a problem, but you want to make sure your database is correctly noralized. Third normal form is a good start.

Answer 4

If you have to ask, "Are there too many fields in this table?" Then there probably are.

Answer 5

A tell-tale sign is just what you said. He has fields that should in theory be split out into a different table. Another giveaway is the presence of many optional fields.

I'd say that a course in database design is in order for your DB "Expert". And I'd suggest that you brush up on it as well...it can only help you grow in your career :)

Answer 6

Database tables can legitimately have 30 or more fields in them. What you need to look at is the normalization of the data and whether that normalization makes any sense. It will normally change in the future, as well. But, you want to try to minimize that.

For instance, if you have a table that has addresses in it, do you include the city, state, and zip code fields in that table? Or, do you only include one field that "points" to a record in a separate table for those values? The separate table would contain unique city, state, zip code combinations. The effect of splitting the data into two tables is a reduction in the amount of data stored (most likely but not absolute) but a bit of added complexity when you go to run queries against the database. Now, you have to deal with 2 tables instead of just one. But, on the bright side, it's much cleaner, and much smaller (likely).

The real answer is it's okay to leave the city-state-zip data in the address table in the right circumstances. Or, you might want to "normalize" it out. Both are okay.

Find a good database administrator and hire them short term to review the plan, if it's in the budget. It will pay off in the long run.

Answer 7

Thirty fields is not too many - you just have to make sure your data is properly normalized (for which there are plenty of guides on the web).

Based on your edit where you specify that many columns will be option-type fields which may be added or deleted over time, I would suggest the following is a better idea.

BaseTable:
    Id
    NonOptionFields
OptionTable:
    Id
    OptionName
    OptionValue

Then you can tie all your options to the base record. This will mean you won't have to be adding and deleting columns to tables all the time ly normalized way to achieve what you want.

Answer 8

The most obvious sign a table requires normalization that I've seen is fields ending with integers: CouponCode1, CouponCode2, CouponCode3.. you get the point. There will be exceptions to the rule as always though.

Answer 9

The guerilla's guide to normalization-by-default:

1. A table should have a primary key and at most one other column.
2. Break rule number 1 only as often as required.

Answer 10

There is no constraint on number of fields in database theory. A table can be limited to a primary key (even if this primary key is made of 2 fields), meaning that Apocalisp's answer is not very clear. At the opposit, a table can be made out of thousends of fields, as long as normal form rules are respected.

When groups of fields are obviously underused in a table, it can be smart to split this group of fields in another table with a 0-1 relation between the main table and the "sub" table.

For security reasons, it was also often proposed (a long time ago: i think it was my first book of relationnal databases, first published in 197?) to split the confidential infos in another table with the same 0-1 relation between main and sub. It was then possible to easily restrict user access to "sub" table. Such a configuration can now be easily managed through views.

Answer 11

The term "too many" is a relative one... You shouldn't split a table only for the sake of reducing the number of fields, especially if in every query you're going to have to join them back together because they are essentially one-to-one relationships. If the fields can be broken down into a separate, logical object then it would make sense. For example instead of storing address fields on a customer table, they could be moved into a separate address table. This is a crude example, but it illustrates my point.

Answer 12

From my experience of designing databases, there are very few tables in a normalized OLTP database that contain an insanely large number of columns.

IMO 30 columns is too many.

For me, no more than 10% of my OLTP tables have a large number(>10) of columns.