> >> I may just have had an insight over my morning coffee. > >> How about turning things around and adding a FK -to the customers table- > >> 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 normalised. > >> What do you think ? > >> > >> > > I think you just made my point. > > > > You now recognize that designing it "right" introduces other complexities. > > > > With the problem you presented, it is just a matter of where you want to get > > tasered. There isn't a solution that optimizes all parameters. > > > > > Hey, > > 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.
Exactly. > 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 as well (so you don't have to check all other tables for it) and keep in mind that a customer can go from a "person" to a "company" etc... Good luck. Martijn Tonies Database Workbench Lite for MySQL - FREE developer tool for MySQL! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
