Building a Contact Database - Need a little schema inspiration.

Question

I've been working on laying out the data structure for an application I'm working on. One of the things it will need to handle is storing customer / contact information. I've been studying the interface of a few different contact information programs like Address Book, gmail contacts, etc.

I have basically boiled the contact down to an "entity" (individual, company, role, etc).

• Each Entity can have multiple Address, Phone, E-Mail entries.
  
  • Each of these defines a "relationship" (home/work/assistant, etc)
  • Entity {1} --{relationship}--> {0..*} Data
• An Entity can have multiple fields that are freeform data storage for other "generic" data (birthdays, AIM account, etc)
  
  • Entity {1} --{fieldName}--> {0..*} Field Data
• An Entity can link to another Entity for instance as a employee, spouse
  • Entity {0..} <--{relationship}--> {0..} Entity

Has anyone done any SQL implementations of similar contact databases? Any insight/suggestions/pitfalls to avoid that you could share with someone trying to work on a project by themselves here? Does what I have described seem reasonable or overcomplicated?

One question, lets say you have 4 people who all work for the same company. They all have the same "work" phone number (perhaps with a different extension) - If the number or address for "work" changes, I'd like to be able to update the contacts fairly easily. Now a lot of this comes down to how you would use the database. I'm thinking that this becomes a matter of linking employee's to their respective company entities, but then the address/phone number is no longer directly connected to the employee. I'm kind of debating making the Entity/data relationship many to many allowing you to attach the same mailing address/phone number to multiple people, and updating it in one place can update it in all places. Am I just over thinking this? pulls out hair

Answer 1

It's a bit trite, but you need to know your data and what you're going to do with it before you start thinking tables.

I'd suggest looking at Object Role Modeling (or this too) to define the model in plain English before you actually implement any tables. I use this VS plugin: NORMA that will also generate a schema for you too.

Alternatively, there are a bunch of data models here that may inspire you. This is "Contact Management" but there are others, such as the "Customers" section

(I just wanted to post an image..)

Answer 2

A couple of points that tend to crop up in my experience..

Consider reciprocal relationships. For example, if someone is defined as being an employee of a company, then the company is then by definition the employer of that person.

Are you considering addresses as entities in themselves, or as merely free text associated with a person/place? In some applications, the address (physical building etc) is 'real' and only one can exist in the table. (Often using the government/postal identifier for it).

Answer 3

This is just to help with your second question; where phone extension actually belongs to relationship between person and company entities.