Spidey Posted August 1, 2011 Posted August 1, 2011 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 Posted August 1, 2011 Posted August 1, 2011 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 Posted August 2, 2011 Author Posted August 2, 2011 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 Posted August 2, 2011 Posted August 2, 2011 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 Posted August 2, 2011 Posted August 2, 2011 ... 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 Posted August 2, 2011 Posted August 2, 2011 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 Posted August 2, 2011 Posted August 2, 2011 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 Posted December 28, 2011 Author Posted December 28, 2011 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. :)
Recommended Posts
This topic is 4734 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now