title: Building a Contact Database - Need a little schema inspiration
body: <p>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. </p>
  
  <p>I have basically boiled the contact down to an "entity" (individual, company, role, etc).  </p>
  
  <ul>
  <li>Each <strong>Entity</strong> can have multiple <strong>Address</strong>, <strong>Phone</strong>, <strong>E-Mail</strong> entries.  
  
  <ul>
  <li>Each of these defines a "relationship" (home/work/assistant, etc)</li>
  <li><strong>Entity</strong> {1} --{relationship}--> {0..*} <strong>Data</strong></li>
  </ul></li>
  <li>An <strong>Entity</strong> can have multiple <strong>fields</strong> that are freeform data storage for other "generic" data (birthdays, AIM account, etc)  
  
  <ul>
  <li><strong>Entity</strong> {1} --{fieldName}--> {0..*} <strong>Field Data</strong></li>
  </ul></li>
  <li>An <strong>Entity</strong> can link to another <strong>Entity</strong> for instance as a <em>employee</em>, <em>spouse</em>
  
  <ul>
  <li><strong>Entity</strong> {0..<em>} &lt;--{relationship}--> {0..</em>} <strong>Entity</strong></li>
  </ul></li>
  </ul>
  
  <p>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?</p>
  
  <p>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? <em>pulls out hair</em>  </p>