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.

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?

share|improve this question
add comment

4 Answers

up vote 3 down vote accepted

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.

share|improve this answer
    
What happens when there is another type of contact that needs to be accounted for? You're talking about db object creation (the least of the problems), application code change, etc. –  Thomas Stringer May 14 '12 at 13:06
    
In the event that an entity is required that has more than a few differing fields from Individuals and Organizations, a third table could be created. –  Leigh Riffel May 14 '12 at 13:42
    
That is a major change. Which is why that denormalized structure you suggest is flawed. –  Thomas Stringer May 14 '12 at 13:53
    
See my comments on your answer. Yes, it would be a design change. Unlikely, but acceptable and manageable if it is necessary. –  Leigh Riffel May 14 '12 at 14:36
add comment

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

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.

share|improve this answer
add comment

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:

enter image description here

share|improve this answer
    
What this gains in a bit of flexibility, it looses in simplicity and performance. For example, to display the Individuals would require a query like this SELECT * FROM Contact c JOIN ContactType ct ON ct.ContactTypeId = c.ContactTypeId JOIN ContactProperty cp ON cp.ContactId = c.ContactId JOIN ContactPropertyType cpt ON cpt.ContactPropertyTypeId = cp.PropertyTypeId WHERE ct.Name='Individual' just to get the data. It would need additional logic to aggregate the results into something resembling a table. –  Leigh Riffel May 14 '12 at 14:21
    
The ability to describe data is decreased. Properties of Individuals and Organizations cannot be foreign keys to other tables, or to each other. For example, columns such as RegionNumber, DeliveryPreference, PrimaryContact that lend themselves to foreign keys cannot be. This also gets more complex when information is needed that does not fit smaller character data types. A CompanyNotes Clob field for example or a query to get something like the Average(MinimumPurchaseAmount) for all companies. As far as performance you can't create indexes on ContactStatus, IndividualRegistered, etc. –  Leigh Riffel May 14 '12 at 14:21
    
add comment

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

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.