Table with contacts: how to differ between people and organizations?

Question

In several projects, I've seen the same problem pop up. But so far, I have not found a satisfying solution. Here's the issue. I need a table with contacts. These 'contacts' could be individual people, or organizations. From one point of view, I prefer to have these contacts mixed (one table with both types), but from another point of view, I prefer to have them separated. Here's some arguments:

• A company that sells products to the general public has 'clients' of both types. When relating a sale, it is most convenient to refer to a 'client ID' without having to specify what type of client this is (for each sale).
• A company can have many providers that it uses. Some are individual people (like a plummer or a consultant), others are companies. When relating a 'purchase order' I again prefer to relate to just one provider ID, without having to differentiate between provider types.
• When managing the actual contacts, I do want to differentiate between the two types. That way, I can have the right fields for each. For instance, "First Name" and "Last Name" are not useful for companies, but they do are for people. Also, if I have a separate table, I can actually link people working at specific organizations.

Being pretty common - I guess - I hope that others have tackled this problem before. What is the way you set this up?

Answer 1

I suggest you create them as separate tables.

• Create an Individuals table and an Organizations table so that each can have the proper fields for their respective types.

• Draw from the same sequence to populate the ClientID field in both tables.

• Create a view to UNION ALL both tables for code that needs information about both.

• Create foreign keys to the tables as necessary.

Answer 2

More than "how" will you model it, consider "where" will you be implementing it. If for example, you are going to work on EntityFramework, check differences between TablePerHier, TablePerType and TablePerConcretetype . Having seen that the answer in your case should be more evident.

I for starters prefer TPT. Is normalized and enforces referential integrity, but that also assumes o have more control over the DB so you can write triggers and other fancy sprinkles

Answer 3

Did you try having them in the same table and keeping a cardinality n:m?, You can try this way..

Where type is P(personal) or O(organization). contact_has_contact is the relation table with the FK of each.

or you could have first_name and last_name as field, and if the type is O, the last_name field is null.

Answer 4

You could have a very normalized structure, like the following. Your base table would be:

create table Contact
(
    ContactId int identity(1, 1) not null -- or whatever you chose for this
        primary key clustered,
    ContactTypeId int not null
)
go

Then your lookup table for ContactType:

create table ContactType
(
    ContactTypeId int identity(1, 1) not null
        primary key clustered,
    Name nvarchar(128) not null
)
go

Add your foreign key constraint

alter table Contact
add constraint FK_Contact_ContactTypeId
foreign key (ContactTypeId)
references ContactType (ContactTypeId)
go

So that handles the relationship between contacts and their types. Now onto the data of the contacts. Create a table to store the contact property types:

create table ContactPropertyType
(
    ContactPropertyTypeId int identity(1, 1) not null
         primary key clustered,
    Name nvarchar(128) not null
)
go

And then lastly, create the join table for contacts, their property, and the value associated with that:

create table ContactProperty
(
    ContactId int not null
        foreign key references Contact (ContactId),
    PropertyTypeId int not null
        foreign key references ContactPropertyType (ContactPropertyTypeId),
    Value nvarchar(128) not null
)
go

With this structure, you have a normalized architecture that has endless scalability possibilities. For viewing purposes, this is a visual diagram of the above: