SQL: Multiple Columns

normalization

Generally, a table design with repeated ambiguous columns is bad design:

CREATE TABLE persons(
    personid int primary key,
--  …,
    phone1 char(10),
    phone2 char(10),
    phone3 char(10)
);

while a table with distinct columns is better design

CREATE TABLE persons(
    personid int primary key,
--  …,
    mobile char(10),
    homephone char(10),
    fax char(10)
);

Understood and Agreed.

The problem is that as far as I can tell, the first design scrapes through the most definitions of normalisation, since technically the columns have different names. Databases neither know nor care what the names mean, and it has always up to human interpretation as to what to put where.

Clearly, he problems with the first design include:

  • the arbitrary number of columns is often too many or to few for some rows; this may even require altering the table to accommodate.
  • because the names are ambiguous
    • it is possible that some of the earlier columns are empty while the latter ones are populated
    • it makes searching difficult since you don’t know which column to search

I also know that if there are genuinely multiple values then the data should have appeared in a separate table.

The question is:

  • is there a correct name in the literature for the anomalies caused by the first design?

  • is there a “normal” form which precludes the first design?

Best Answer

I'm not sure on what the anomalies caused by the first design are called, but the below is the standard solution for that type of problem. Or at least, it's the standard solution that I use.

Thea answer is to create another table to hold just phone numbers with a foreign key reference to the person table. The only sticking point may be the PhoneTypeDescription field which should probably be yet another lookup table, depending on how many phone number types you think you might store. But possible values could be Work, Cell, Home, Fax. The use of the "IsPrimaryPhone" is also optional but probably warranted.

CREATE TABLE dbo.Person
    (
    PersonID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
    , FirstName NVARCHAR(100) NOT NULL
    , LastName NVARCHAR(100) NOT NULL
    )

CREATE TABLE dbo.PhoneNumbers
    (
    PhoneNumberID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
    , PersonID INT NOT NULL REFERENCES dbo.Person(PersonID)
    , PhoneTypeDescription NVARCHAR(10) NOT NULL
    , PHoneNumber NVARCHAR(20) NOT NULL
    , IsPrimaryPhone BIT NOT NULL DEFAULT 0
    )

CREATE NONCLUSTERED INDEX IDX_PhoneNumber_PersonID ON dbo.PhoneNumbers (PersonID, PhoneNumberID)
Related Question