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
