Flexible Contact Detail table structure

[santlou]

Hi,

I have a standard database designed to maintain contact information. I'm trying to design a table structure for my Names Table that will support both personal and business names.

For example, I have my primary table that contains a company name and misc info about the company. Then for each company, there will be one or more contacts. These contacts can be people, departments, other companies, or any other entity.

So, at a high level, my database looks like this:

Company Table:

CompanyID
CompanyName
CompanyType
etc...

------------------

Address Table:

AddressID
AddressType
EntityID
EntityType
StreetLine1
StreetLine2
City
State
Zip
Country

----------------------

Contact Table

ContactID
ContactType
EntityID
EntityType
FirstName
Middle
LastName


------------------------

Contact Details Table

ContactDetailID
ContactDetailType
EntityID
EntityType
ContactDetail

---------------------------------------------------------

My issue is with the Contact Table

The contact table contains names of contacts associated with a Company (EntityType='CO')

So if a company has two people, it would look like this:

Company Table  Row1

CompanyID      849
CompanyName    Exxon
CompanyType    BS

-----------------------------------------------------
Contact Table  Row1       Row2

ContactID      1009       1010
ContactType    OWNER      Salesman
EntityID       849        849
EntityType     CO         CO
FirstName      Thomas     Johnny
Middle         R
LastName       Jones      Cash

------------------------

Contact Details Table   Row1             Row2

ContactDetailID         5213             5213
ContactDetailType       OFFICE PHONE     Mobile Phone
EntityID                1009             1009
EntityType              CN               CN
ContactDetail           [phone]    [phone]

OK.. here is what I want to do: Often, the contacts are not individual people. Instead the contacts can be departments, vendors, other companies, etc.

So my contacts table will now look like this:

Contact Table  Row1       Row2       Row3       Row4

ContactID      1009       1010       1011       1015
ContactType    OWNER      Salesman   DEPT       MISC
EntityID       849        849        849        849
EntityType     CO         CO         CO         CO
FirstName      Thomas     Johnny     Customer   Website
Middle         R                                Customer
LastName       Jones      Cash       Service    Support

As you can see, the Customer Service and Website Customer Support contacts simply should not be in the First, Middle, Last fields. So.. my issue is How do I structure my Contacts table to support both Individual and Non-Individual names?

I was thinking of adding another field called FullName to the table. This way when the data is populated, it will be populated into the appropriate structure (i.e. First, Middle, Last for people, and FullName for 'Unstructured' names.

Any advice would be appreciated.

Thanks