Contact system database design

database-design

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?

Best 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.

Related Solutions

How to design relationships for variant data

This might force a bit of change for your tables, but we do something like this at work:

Ensure each table has a int/guid primary key of some sort:

Organization Table

ID | Name
1  | MyOrganization

Then create a type-table to house your different LeadSource types:

LeadSourceTypeKey | Description
1                 | Organization
2                 | Company Office

Then on your ProductSoldList Table add 2 columns LeadSourceID and LeadSourceType

Then you can easily query the ProductSoldList TABLE and know which LeadSource to join back to:

SELECT *
FROM   [ProductSoldList] PL
       INNER JOIN [Organization] O
       ON PL.LeadSourceID = O.ID AND
          PL.LeadSourceType = 1
       INNER JOIN [Company Office] CO
       ON PL.LeadSourceID = CO.ID AND
          PL.LeadSourceType = 2

User system database design

Having the users and lost passwords in the same table implies a 1-1 (or 1-0/1) relationship; 1 user has 1 lost password (or none, if the column is nullable).

If that's the cardinality of the relationship, having them on the same table is correct. The school of thought that refuses nulls (which depending on the day, I adhere to), says that if the relationship is 1-0/1, you should:

users
  usersID primary key

lostPasswords
  usersID primary key references users(users_id)
  -- lost password columns here, non-nullable

The relationships look fine, however, I would suggest you use a diagramming tool which shows which are the columns referenced. Surprisingly, I've only found SchemaSpy doing this: http://schemaspy.sourceforge.net/sample/diagrams/summary/relationships.real.compact.png

Best Answer

Related Solutions

How to design relationships for variant data

User system database design

Related Question