Eric M Russell (1/21/2013)
Customer contact information is an example of where an entity-attribute-value model makes sense. The design is simple, efficient, and can be easily extended to support any type of contact, wether it be G.O.T. style messenger ravens or yet to be defined holograms.create table Customer_Contact
(
primary key clustered ( Customer_ID, Contact_Type ),
Customer_ID int not null,
Contact_Type char(2) not null,
Contact_Code varchar(8000) not null
);
PH = Home Phone
PC = Cell Phone
PW = Work Phone
PA = After Hours Phone
PP = Primary Phone
PS = Secondary Phone
PE = Emergency Phone
EC = Personal Email
EW = Work Email
EP = Primary Email
34592 PH 555-381-0922
34592 PC 555-381-8111
34592 PW 555-576-0542
34592 PP 555-576-0542
34592 EC customer34592@gmail.com
34592 EP customer34592@gmail.com
Personally I would have a PhoneContactPoint table and an EmailContactPoint table
CREATE TABLE PhoneContactPointType(
PhoneContactPointTypeID SMALLINT NOT NULL
CONSTRAINT PK_PhoneContactPointType PRIMARY KEY CUSTERED,
PhoneContactPointType VARCHAR(50) NOT NULL
CONSTRAINT UQ_PhoneContactPointType UNIQUE,
CONSTRAINT CK_PhoneContactPointType CHECK(LEN(PhoneContactPointType)>0)
)
CREATE TABLE PhoneContactPoint (
CustomerID INT NOT NULL
CONSTRAINT FK_PhoneContactPoint_Customer FOREIGN KEY REFERENCES Customer_Contact(CustomerID)
PhoneContactPointTypeID SMALLINT NOT NULL
CONSTRAINT FK_PhoneContactPoint_PhoneContactPointType FOREIGN KEY REFERENCES PhoneContactPointType(PhoneContactPointTypeID),
TelephoneNumber VARCHAR(25) NOT NULL,
CONSTRAINT PK_PhoneContactPointType PRIMARY KEY (TelephoneNumber ,PhoneContactPointTypeID )
)
Ditto EmailContactPoint.
You could even generalise EmailContactPoint to InternetContactPoint if you wanted blog sites and Twitter details