Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm designing a database for contacts and I'm looking for some feedback on the current design.

What i want:

  • A contact can be a person or an organization.
  • A contact can have many groups.
  • A contact can have many emails and addresses
  • A contact can have one organization (employed)
  • A organization can have many contacts (employees)
  • A group can have many contacts.

What i have:

  • Contact(Id, PersonId (FK), OrganizationId (FK), EmployerId (FK, self reference))
  • Person(Id, FirstName, LastName, Birthday)
  • Organization(Id, Name, Founded)
  • Group(Id, Name)
  • ContactGroup(Id, ContactId (FK), GroupId (FK))
  • Email(Id, ContactId (FK), Name)
  • Address(Id, ContactId (FK), Street, City, ZipCode, Country)

What bothers me the most is that the contact entity has one organization or one person, and also a self reference (EmployerId) if a organization has employees (which are also contact entities) and vice versa.

enter image description here

Is this a problem or see you a better way?

share|improve this question
    
Take a look on 'The Data Model Resource Book' by Len Silverston. Party-role-relationship approach perfectly fits your task... –  a1ex07 Jun 14 '13 at 23:33
add comment

1 Answer

Check the "Data Model Resource Book, Vol. 1". It covers standard schemata for - among other things - contact management. Pretty much the whole CRM and accounting etc. side.

And yes, there are a lot of problems with your approach - but I wont replicate 30 pages or so from that book describing all the issues.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.