Data model for all contacts - companies and people (Party!)

ML!

Registered User.
Local time
Today, 06:46
Joined
May 13, 2010
Messages
83
I have researched the 'party' data model but it is a bit too complex for what I'm seeking. For those familiar with it, I don't really need the intermediary relationship from-to tables.

I'm interested in ideas about setting up a data structure that will allow users to search contacts or select contacts in dropdowns regardless if the contact type is a person or an organization.

Obviously the fields needed for both are different and the biggest issue is the name field because the person contacts are

The way I am accomplishing it now is writing the company name, or "first name " & "last name" for a person, to kind of a bridge table when a new record is inserted into the person table or the organization table...kind of inefficient.

Is this a relationship thing or should I just write a function to create a temporary recordset when needed? TIA!
 
You need a company table, a orginazationperson junction table and a person table (firstname, lastname, etc. fields). First and lastname can always be concantenated and sorted to suit in a query. You'll find that evebtually, a person wlll show up in a different organization.
 
I have done this before but with Entity rather than Party, but I have also heard of Agent. A simple table could look like ...
tAgent
AgentID
AgentType
Name1
Name2
Date1
Address1
Address2
... and rather than ever use that table directly, you could write two queries, like ...
SELECT AgentID as PersonID, Name1 As FirstName, Name2 As LastName, Date1 As DateOfBirth, Address1 as Address
WHERE AgentType = "Person"
... and ...
SELECT AgentID as CompanyID, Name1 As CompanyName, Name2 As DoingBusinessAs, Address1 as BillingAddress, Address2 As ShippingAddress
WHERE AgentType = "Company"
I can't see how this would cause any problems, and I can see how it would solve a few.
 
Thanks @llkhoutx and @lagbolt. Love the ideas so far.

lagbolt, I think if the data requirements were simpler, that idea would work great! While the idea about the date1 etc might offer flexibility in some cases, I want to capture pretty specific data for company contacts and very different data for people contacts so there would be a lot of empty fields if I tried to put them together. If I tried to use the generic fields, it might be confusing at the table level. I think the single table might not work for me but maybe I'm wrong - would love to hear more about that idea.

llkhoutx, I have started to work along the same lines as you've mentioned. I have an organization table and a person table then a bridge (or junction table I guess) but that's where I get indecisive. Should the junction table just include foreign keys to the main tables or should it just be a primary key, party type and the name field? The way I have set it up follows...

tblPeople
PeopleID (PK)
PartyID (FK)
ContactDetailsID (FK) foreign key to a table with address phone etc
FirstName
LastName
Birthdate
etc...

tblOrganization
OrganizationID (PK)
PartyID (FK)
ContactDetailsID (FK)
OrganizationName
BusinessStructure
etc...

tblParty
PartyID (PK)
PartyType - person or organization
PartyName - organization name or first name & " " & last name

If your think this would work, then I guess I'd write to the name field and party type field in that table via a function...? Different forms would be used for data entry and updates and searching so a lot of different objects will need to read and write from the combined data so probably a function would work best. Would you agree?

Then if I need that table to point back to the original table after a search, how would I best accomplish that? So for example say a user entered a string in a search text box say like 'smit' the results of a find function might return...

Bill Smith
Smithson Auto Parts
Smitty Jones

Those 'options' would show in a listing with icons to demonstrate that they are people or companies and a checkbox. The user then could select the option they want and click a button to fire another function on the selected record which might have to use data in the original tables - say look up related transactions for the company or the person.

Would love to hear both of your thoughts on that and anyone else's. TIA!
 

Users who are viewing this thread

Back
Top Bottom