David.Poole (1/21/2013)
......
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
Yeah, but to what end? There is no practical reason to partition internet based contacts from telecom contacts in the data model. Most phone or fax calls today are done using an internet enable device, wether it be iPhone, Skype, or magicJack. I still have the same 999-999-9999 I had ten years ago, only it's since been ported over from an analog provider to magicJack.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho