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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
