Customer Conundrum

[Kent Searight]

Hi all,

I've dealt with the following situation in different ways in different instances and I was wondering if anyone would like to put their in:

A database needs to track Customers, People and Companies. The vast majority of People and Companies are, or can be, Customers.

In the past I've solved this in one of two ways.

The first method is to create one single table for People and Companies (similar to the way Mac's Address Book works). This is initially fast and easy but can become a bit cumbersome when you want to track how People relate to Companies and vice versa.

The second method is to create a Customer table, a People table and a Companies table. Whenever a new People record or Companies record is created, a Customer record is created first and that Customer ID is then assigned to the Person or Company (in their Customer ID field). This allows more flexibility when you want to track how People relate to Companies and vice versa, but requires a greater level of control when creating People and Company records.

Any thoughts on either of these methods? Anyone have a better way?

Thanks in advance for any input!

[Søren Dyhr]

This is one of the most debated issues with datamodelling:

And so, whether the use of a single PEOPLE table in your database is

better than having separate tables for each type of people (STUDENTS,

TEACHERS) depends not on some empirical law that one table is better

but on the law of pragmatism– whether it solves a required problem.

In

other words, the test is whether it meets the requirements of the

problem. To know if a structure meets the requirements, we must

first capture and communicate said requirements. Ergo, we must model.

Found here:

http://fmcollective.com/2006/08/24/object-influenced-design-amp-filemaker-pro/#more-14

--sd

[FestiveEmbalmer]

I had to make this very decision yesterday in my own solution.

I have, up to this point, had two distinct tables for persons and Companies/Agencies/Programs. I essentially divided them into the concepts of Individuals and Corporates. But all I was really doing was mentally creating a relationship that wasn't useful at all in my solution- the idea that Corporate entities are made up of Individual entities. There were other issues as well, but basically there is no FUNCTIONAL difference between COMPANIES and PEOPLE in my solution. I created one table, "CHARACTERS" (I'm open to suggestions on the table name!)and whenever there is a functional difference (ie. companies cannot be clients of our organization) I create a record in an appropriate sub-entity table. (ie. "CLIENT_ACCOUNT").

The article Søren linked to seems to confirm my thoughts.

"The first method is to create one single table for People and Companies (similar to the way Mac's Address Book works). This is initially fast and easy but can become a bit cumbersome when you want to track how People relate to Companies and vice versa."

How is this cumbersome, exactly?

[comment]

Just a note: assigning people to companies within a single Contacts table is not much different from doing so with two separate tables. You can use a simple self-join, or a join table to another occurrence of Contacts.

[FestiveEmbalmer]

In fact, isn't it possible to have all relationships be self-joins off of one huge table with lots of fields specific to each pseudo-table that simply remain empty for all other pseudo-table?

...like if you were a masochist?

...a really, really ambitious one?

[Kent Searight]

Here are some downsides to the single table solution:

If a functional requirement is to display Companies in their own list view, extra steps are required to ensure that only Companies appear in the list. Non-scripted finds will not meet this criterion. Ditto for People.

Layouts for People and Companies can get cluttered to the point of distraction if they display lots of data that's specific to one or the other. List views may require very different data to be displayed between Companies and People.

Don't get me wrong about the single Contacts table approach. I've used it and I like it (sometimes). It just has its downsides.

As Michael pointed out, using self-joins or a separate join table (Employment) handles the Company to People issue. I should have been more specific about what I find cumbersome in the single table model.

Thanks for all the input from everyone thus far!

[FestiveEmbalmer]

Excellent.

These thoughts are relevant to my interests.

Now having started a post with "excellent" I am compelled to end it with an appropriate quote:

"Do my worst, eh? Smithers, release the robotic Richard Simmons."

[Kent Searight]

On the flip side, the 3 table model has it's share of downsides. One in particular is doing scripted sorts-by-name in a list view of Customers. Here's why.

The Customers table, which the list layout is based on, only holds the Customer ID of the related entity, either People or Companies. The name displayed in the list is either from the People_of_Customers TO or the Companies_of_Customers TO (done by stacking one transparent-background field on another).

How do you sort them easily by name so that Company names and Person names sort in true, alphanumeric order?

[FestiveEmbalmer]

I had a calculated field called Name_Full that checked another field to see if it was a Person or Company, displaying the appropriate name.

Of course mine is the single table solution, but it's still applicable.

[Kent Searight]

Having an unstored calc field in the Customers table would cause too much of a performance hit over the network it runs on.

I really don't want to store anything other than the Customer ID (and the standard create & modify fields) in the Customers table. This eliminates any need for maintenance in that table and maximizes performance.