How may I store contact methods (or similar typed data with different fields) in a normalized way?

Question

I've crossed this problem in, oh, every application I've ever written. I'd like a final consensus answer!

Is this the most normalized/efficient/proper way to store contact methods or any other data along this pattern in a database?

Contact { PK ContactId, ... }

ContactContactMethod {PK FK ContactId, PK FK ContactMethodId, Key, Comments }

AddressContactMethod {PK FK ContactMethodId, Line1, Line2, City, State, Zip }
EmailContactMethod {PK FK ContactMethodId, EmailAddress }
InstantMessengerContactMethod {PK FK ContactMethodId, IMClient, IMAddress }
PhoneContactMethod {PK FK ContactMethodId, ... }
WebsiteContactMethod {PK FK ContactMethodId, ... }
OtherContactMethod {PK FK ContactMethodId, ... }

Should I consider an Xml field for ContactMethodData on the ContactContactMethod table instead?

Something just feels wrong about AddressContactMethod, EmailContactMethod, etc all sharing the same uniqueness of primary keys.

Also thought about a key, value pair for contact data, but that would be even more of a pain to query than the Xml field.

(Design guidelines: each contact may have more than one or none of each type of contact method, each with a non-unique "key" like "home, work, red car, etc" and comments but no other shared data elements between types)

Answer 1

Most people really over architect things. Look at it this way, how many phone numbers for a particular Contact do you really care about? Usually 1; sometimes 2.

The same for addresses, usually all you really need is the one on file.

So, with that in mind, look at your app. If you really only have 2 phone numbers, store those in the CONTACT table as HomePhone, WorkPhone or whatever. If, at some point in the future, you determine you need more phone numbers then go ahead and break the numbers out into a separate table. Same for addresses. Same for email.

The reality is that most UI's don't show phone numbers in a grid. Instead they are placed right in line with the name of the person. The reason being is that you only need a couple of numbers.

To sum up: keep the database as simple as it can be. The UI team, and your database server, will thank you for it.

Answer 2

In the olden days I'd have gone with a table schema - something like:

Person [ID, Name]
ContactPoint [ID, PersonID, ContactMethod]
EmailContactPoint [ID, EmailAddress]
AddressContactPoint [ID, Line1, Line2, blah]

With a 1-1 relation between the different contact points and the ContactPoint table - a kind of inheritance. ORM's like Hibernate/NHibernate and Entity Framework can instantiate the correct type based on these 1-1 relationships.

Nowadays I'd probably just go with an XML blob, and a couple of functions in SQL to make it easy to deal with the default/primary contact point for a particular type (e.g., "GetPrimaryPhoneNumber(personID)".

Answer 3

The final consensus answer is that everyone's own answer is the best and no-one else's is worth a cent. That's the problem; there isn't a single way that suits everyone's needs. So, there probably never will be a final consensus answer - maybe not even a consensus answer but almost certainly not a final one.

Answer 4

Like you, I've seen more than my share of contact information database designs. I think yours is pretty reasonable, and locating the Key column separately from the *Method tables is a nice touch, allowing grouping of "home" e-mail and postal addresses to be grouped after a fashion.

However, I think most contact databases are over-architected. While I don't agree with Chris Lively's approach, I think you could simplify by just having types of contact information (e-mail, phone, web url, etc) and storing a simple text string for each. Trying to validate the separate types or breaking them up into sub-fields isn't usually worth the effort. For one thing, all telephone and address validation rules immediately go out the window if you allow for contacts outside of the US. Store the full address in a Unicode string, and hope for the best is my advice.

That leaves a design something like this:

Contact { PK ContactId, ... }
ContactType { PK ContactTypeId, ContactType }
ContactMethod {PK FK ContactId, PK FK ContactTypeId, PK Key, Value, Comments }

The ContactMethod.Value is the text value.

That seems similar to how Google tracks contacts at the very least. And if nothing else, they've likely thought through the problem of keeping track of zillions of contacts from every nation on Earth.

Answer 5

There is a name for this pattern. It's called "specialization generalization".

In this case Contact Methods are specialized ways of contacting a Contact.

A web search on "generalization specialization relational design" will lead you to some articles dealing with this pattern in a more general fashion. For an object orineted view of the same pattern, search on "generalization specialization object design".