Too many columns in single table - is it good normal form?

Question

A normalized table should have less number columns and can have reference fields as much as possible. Is it right approach? Is there any relationship between number of columns and a good normalization process?

Answer 1

You should follow the normalization principles rather than be concerned with the sheer number of columns in a table. The business requirements will drive the entities, their attributes and their relationship and no absolute number is the "correct" one.

Answer 2

Is there any relationship between number of columns and a good normalization process?

In short, no. A 3NF normalized table will have as many columns as it needs, provided that

data within the table is dependent on the key, the whole key, and nothing but the key (so help me Codd).

There are situations where (some) denormalization may actually improve performance and the only real measure of when this should be done is to test it.

Answer 3

While I agree with @ocdecio, I would also observe that a database that is normalized will generally have fewer columns per table and more tables than one that is not, given the same data storage requirements. Similar to code smells a database smell would be relatively few tables given a reasonably large application. This would be a hint that perhaps your data is not in normal form. Applying normalization rules, where appropriate, would alleviate this "smell".

Answer 4

Here is an approach you can use if you feel your table has too many fields. Example:-

CREATE TABLE Person
    Person_ID int not null primary key,
    Forename nvarchar(50) not null,
    Surname nvarchar(50) not null,
    Username varchar(20) null,
    PasswordHash varchar(50) null

This table represents people but clearly not all people need be users hence the Username and PasswordHash fields are nullable. However its possible that there will be 1 or 2 orders of magnitude more people than there are users.

In such case we could create a User table to hold the Username and PasswordHash fields with a one-to-one relationship to the Person table.

You can generalise this approach by looking for sets of nullable fields that either null together of have values together and significantly likely to be null. This indicates that there is another table you could extract.

Answer 5

Each column must have a direct and exclusive relationship to the primary key. If you have an attribute-heavy item that there is only so much you can do to simplify the model. Any attempt to split into multiple tables will be counter-productive and pointless.