> >> I may just have had an insight over my morning coffee.
> >> How about turning things around and adding a FK -to the customers
> >> on each of the customer type tables (companies, people, charities, etc)
> >> The customers table would have no idea if a customer is corporate or
> >> private, it just has a customer number that can be used in processing
> >> invoices and performing account maintenance.
> >> The companies, people, charities, etc. tables would each have a FK to
> >> the customers table.
> >> This does off course mean that creating and sorting a list of all
> >> customers is more complex, but the database would at least be
> >> What do you think ?
> > I think you just made my point.
> > You now recognize that designing it "right" introduces other
> > With the problem you presented, it is just a matter of where you want to
> > tasered. There isn't a solution that optimizes all parameters.
> I have to disagree.
> Any application is and always will be complex.
> Having the database refuse to screw itself up whenever the programmer
> makes a mistake, and he/she always will, is a great step towards the
> goal of simplification and robustness.
> Moreover, apart from the sorting problem in this design, I think the set
> of queries in general is much more simple than it would have been had I
> used one of the options from my previous line of thinking.
> I think you can never go wrong with normalized databases.
Because you can query a table and get data for which you know what
it means instead of having to "guess" that columns are used and what not.
In general, you should not have NULL values in your database (as opposed
to NULLs in your result-sets, that's quite different!). There's no point in
storing what you don't know.
> Anyway, I think my question has been answered. Always nice to answer
> your own questions :)
> Thanks for all the comments.
Yep, you're setup seems to be the correct one on this, define the properties
for a "customer" that are common and put those in the CUSTOMERS table,
makes perfect sense.
It could be wise to keep track of what type of customer it is in that table
well (so you don't have to check all other tables for it) and keep in mind
a customer can go from a "person" to a "company" etc...
Database Workbench Lite for MySQL - FREE developer tool for MySQL!
Database development questions? Check the forum!