List:General Discussion« Previous MessageNext Message »
From:Bill newton Date:November 14 2008 6:17pm
Subject:Re: normalised designs: customer database
View as plain text  
Hi everybody,

What about simply storing a main user for each company?  So each company 
has a main user that has the basics about the company ( generic address, 
email, phone). I would think you'd want to store that information 
anyways. So the customer table always links to a user. That user could 
be the company's main user or a user that's not affiliated with a company.

There is more general question here as well which would be unavoidable 
if companies and users were not as closely related. I've seen may 
implementations like your three suggestions as well as the fake company. 
If I had to choose one of those, I"d go with the dual foreign keys. #3

Thanks for the meaty question,

Bill
metastable wrote:
> 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) info@stripped http://www.metastable.be BTW-BE
> 0873.645.643 bankrek.nr. ING 363-0106543-77
>
>
>
>
>   


-- 
Bill Newton
Network Merchants Inc.
http://www.nmi.com
(847) 352-4850 ext 141/ Tel
(888) 829-3631/ Fax

Thread
normalised designs: customer databasemetastable14 Nov
  • Re: normalised designs: customer databaseMark Goodge14 Nov
    • Re: normalised designs: customer databaseMr. Shawn H. Corey14 Nov
      • Re: normalised designs: customer databasemetastable14 Nov
  • RE: normalised designs: customer databaseJerry Schwartz14 Nov
  • Re: normalised designs: customer databaseJujitsu Lizard14 Nov
  • Re: normalised designs: customer databaseMartijn Tonies14 Nov
    • RE: normalised designs: customer databaseUS Data Export14 Nov
      • Re: normalised designs: customer databasemetastable14 Nov
  • Re: normalised designs: customer databaseMartijn Tonies14 Nov
    • Re: normalised designs: customer databaseJujitsu Lizard14 Nov
      • Re: normalised designs: customer databasePeter Brawley14 Nov
  • Re: normalised designs: customer databaseMartijn Tonies14 Nov
  • Re: normalised designs: customer databaseMartijn Tonies14 Nov
  • Re: normalised designs: customer databaseMartijn Tonies14 Nov
    • Re: normalised designs: customer databaseJujitsu Lizard14 Nov
      • Re: normalised designs: customer databasemetastable14 Nov
    • Re: normalised designs: customer databasemetastable15 Nov
      • Re: normalised designs: customer databaseJujitsu Lizard15 Nov
        • Re: normalised designs: customer databasemetastable16 Nov
          • RE: normalised designs: customer databaseJerry Schwartz17 Nov
            • Re: normalised designs: customer databasemetastable17 Nov
  • Re: normalised designs: customer databaseBill newton14 Nov
  • Re: normalised designs: customer databaseMartijn Tonies17 Nov
  • Re: normalised designs: customer databaseMartijn Tonies17 Nov
  • Re: normalised designs: customer databaseMartijn Tonies17 Nov
    • RE: normalised designs: customer databaseUS Data Export17 Nov
  • Re: normalised designs: customer databaseMartijn Tonies17 Nov