List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:November 17 2008 9:11am
Subject:Re: normalised designs: customer database
View as plain text  

> >> 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

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