Layout for Customer Table

[Spidey]

I have a potential client who has customers from both companies and individuals. What is the best practice to design the customer information? Thanks.

Kent

[Vaughan]

What you're asking is: should I have separate tables for companies and individuals, or have both in the same table?

I've worked with both arrangements and, unfortunately, there are pros and cons for both and neither stands out as an absolute "right way". It depends on other business requirements.

From a technical design perspective I prefer a single table but this gets messy when the business requires contacts to be linked to companies.

[Spidey]

Thank you for your reply. I was thinking how to link the individuals and companies with contact together in a single table, but it is too complicated for me to do it properly and maybe difficult to manage in the future...I guess I should separate the table...

Thanks!

cheers,

Kent

[comment]

I guess I should separate the table...

And I suppose your next question will be how to search for "Smith" and find both "John Smith" and "Smith & Company"...

[Vaughan]

... how to search for "Smith" and find both "John Smith" and "Smith & Company"...

That is the *other* messy part.

With combined comp and contact tables the complexity is in linking the companies to contacts, or allowing contacts without companies. With separate tables the complexity is linking the tables to the rest of the solution.

As I mentioned, I have not been able to determine which is "best" without a lot of consideration going into the business needs.

[comment]

I agree that there is no "best" solution. However, having two tables is no solution at all. If you want to track an entity of Customers you must have a table where each customer has a unique record. So it's either one table of Customers or three tables: a supertype table of Customers and two subtype tables.

I am not sure linking contacts to customers is part of this problem.

[RodSierra]

Not sure this is help to anyone, but thought I'd share what we've settled on that seems to work quite well for the last few years.

Having dealt with adding contacts for about every conceivable type of business transaction as fields to one table, we've settled on two tables with a third table providing a cross reference.

customers/suppliers

contacts

Contact reference- containing contactid, cust/suplid and category.

As Customers can be suppliers, can be Companies, can be individuals, can be shipping agents, etc.

A document has only one reference to cust/supl table, whether individual or Company.

Adding records to the contact reference, we can define types of contacts for each cust/supl, for instance purchasing contact, customer service contact, logistics, consigned inventory and so on. We then set a field in our document headers prefs that sets the preferred recipient type, that combined with the selected cust/supl code via relationship will set where any correspondence is to be sent, along with all the related bill to or ship to info to be set in the document itself. The single referenced cust/suplid is always the same despite contact changes. This gives us unlimited flexibilty with regards to setting who gets what type of info, but still establishes one cust/supl rec as the definitive for that document.

[Spidey]

Thank you for your reply. Just wondering in the contacts table, does it only contain other information such as address, phone number, etc and for customer table, it only contains the names of the company or individuals? And in the cross reference, it contains types of contacts and contactid and customerid? Thanks for you input. :)