Database design decision: contact information

[Larry Kahm]

I need some advice with a design decision. Customer wants to track people,
along with various means of contacting them.

The easiest way is to have the fields CellNumber1, CellNumber2, EMail1,
EMail2, FaxNumber built into tblPeople.

An alternative is to have tblPeople linked to tblContactInfo which is linked
to tblContactMethods, thus providing "n" number of contact methods and the
various modes.

Am I making more work for myself by using the latter approach?

Thanks!

Larry

 

[Tom van Stiphout]

On Sat, 09 Feb 2008 16:46:53 GMT, "Larry Kahm"

The latter is much better, and can also accommodate contact methods
that are new or upcoming. If you go that way, you may also need a
IsPrimary flag, so that for example an email blast is sent to the
person's primary email address.

-Tom.

 

[John W. Vinson]

I need some advice with a design decision. Customer wants to track people,
along with various means of contacting them.

The easiest way is to have the fields CellNumber1, CellNumber2, EMail1,
EMail2, FaxNumber built into tblPeople.

An alternative is to have tblPeople linked to tblContactInfo which is linked
to tblContactMethods, thus providing "n" number of contact methods and the
various modes.

Am I making more work for myself by using the latter approach?

I've got a Contacts module that I've used several times successfully. It's not
perfect - there are some one to many relationships that really should be many
to many, as multiple people can share one phone number, for example - but it's
pretty usable:

CONtblPeople
ContactID
LastName
FirstName
<etc>

CONtblAddresses
AddressID <autonumber PK>
ContactID
Address1
Address2
City
StateProvince <two letter code for North America>
Postcode
Country <UN country code>

CONtblPhones
PhoneID <autonumber PK>
ContactID
Phone <text>
PhoneType <Text, from a Phonetypes lookup table>

CONtblEmail
<well you get the idea>

 

[Larry Kahm]

John,

This approach looks more promising and, as Tom indicated in a previous post,
it is probably my best bet.

Back to design mode - because I'm probably going to have to build a similar
structure for Companies....

Thanks!

Larry