List:General Discussion« Previous MessageNext Message »
From:Mark Goodge Date:November 14 2008 2:30pm
Subject:Re: normalised designs: customer database
View as plain text  
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.

It's more complex than that, because there isn't a clear distinction 
between individuals and companies. A customer may be a company, a 
partnership, a charity, a sole trader or a non-trading individual. All 
but the non-trading individual may have (but are not necessarily 
required to have) a VAT number.

> 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
> 
> 
> What do you guys think about this ? Which option is most viable ? Which
> solution have you chosen ?

What I'd do is have a table for "customer", which is essentially the 
invoice information (since that is, effectively, what defines a customer 
to you). I'd have separate tables for alternate addresses and contacts. 
The customer table would have a field defining the customer type, and 
another field containing an id which is the key to further information 
about that customer in the relevant table, where necessary.

I wouldn't try to arbitrarily normalise the database for SQL efficiency. 
In a real-life situation, it's more important that the database design 
reflects your actual workflow and business requirements. Having a field 
that's empty 50% or more of the time is far less of a problem than not 
being able to process a sale because your database structure is too 
inflexible :-)

Mark
-- 
http://mark.goodge.co.uk - my pointless blog
http://www.good-stuff.co.uk - my less pointless stuff
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