2

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)
  • An 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 an 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?

  • 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
-1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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