Hello all,
I have a question that's been bugging me for quite some time. Let's say we have a small business that has both private and corporate customers. We want to store contact and address data about these customers, as well as invoicing data. Off course, only companies have VAT numbers. When normalising this design, you would reach something like the following: - table for contact details (separate, because multiple contact details may apply) - table for address details (separate, because multiple addresses may apply) - table for people (first name, last name, etc) - table for companies (company name and vat number) - tables that link the above data to each other (people-contact, people-address, people-company, company-address, ...) - table for customers, i.e. 'entities' that are invoiced This is where it gets nasty. A customer may be a human being or a company. I see different approaches here: 1) keep customer tables separate, based on which type of customer it is 2) create the customer table with a column specifying if we're dealing with a human being or a company 3) create the customer table with a FK for people and a FK for companies, and decide on the customer type in the application based on the presence of that key Option 1 seems to ridiculous to do: way too much bloody hassle Option 2 may be viable, but we lose the foreign key constraint Option 3 seems like the best idea, but this would mean keeping a table that takes 50% of useless space Neither option satisfies me. My idea right now is a 'False Company' approach: Create a row for a company that is no real company and link all private customers to that company. This way, the customer table has 1 FK, for the companies table. This off course implies that some global variable be present in the application that is used to identify the 'False Company'. Awkward to say the least. What do you guys think about this ? Which option is most viable ? Which solution have you chosen ? And finally: am I going about this all wrong, because I was asleep during most of my formal education years ? :) Kind regards, Stijn -- metastable Stijn Verholen Camille Huysmanslaan 114, bus 2 B-2020 ANTWERPEN +32 (0)3 707 08 08 (phone) +32 (0)3 707 06 06 (fax) +32 (0)473 47 62 88 (mobile) [EMAIL PROTECTED] http://www.metastable.be BTW-BE 0873.645.643 bankrek.nr. ING 363-0106543-77 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
