List:General Discussion« Previous MessageNext Message »
From:Jujitsu Lizard Date:November 14 2008 5:50pm
Subject:Re: normalised designs: customer database
View as plain text  
On Fri, Nov 14, 2008 at 10:45 AM, Martijn Tonies <m.tonies@stripped>wrote:

>
> > > This is where it gets nasty. A customer may be a human being or a
> > > company. I see different approaches here:
> > > 1) keep customer tables separate, based on which type of customer it is
> > > 2) create the customer table with a column specifying if we're dealing
> > > with a human being or a company
> > > 3) create the customer table with a FK for people and a FK for
> > > companies, and decide on the customer type in the application based on
> > > the presence of that key
> > >
> > You're making it more complicated than it needs to be.
> >
> > A customer may be either a person or a company.
> >
> > Your customers table may contain columns that are the union of what is
> > required for a person and what is requried for a company, plus of course
> an
> > enumerated value that indicates which the customer is and indirectly
> which
> > columns are populated for a given row.
> >
> > Problem solved.  Over time, several square millimeters on a disk wasted.
>
> Back to "database design" class for you ;-)
>
> Store in a table what you need to store, storing "which columns are
> populated
> for a given row." is complete rubbish.
>
> By retrieving data you should -know- what data it is, not having to
> retrieve
> a value that indicates what the data actually means.


The use of "should" is a bit tricky.  By whose standard?

The notion of a "variant record" exists in many programming languages.
Typically you have a selector to indicate which variant it is.  There is
nothing at all wrong with using the same sort of construct in a database
table.

http://en.wikipedia.org/wiki/Variant_record

The only constraint you _really_ need to meet in a database is that you let
the database product do the things it needs to do so that the queries you
make are O(log N) when possible.  The rest is pure fluff.  Beyond that,
there is no "should".

The nature of the problem presented by the OP is that all possible solutions
are awkward in one way or another.  It is just a question of where you want
to take the awkwardness.  There is no right answer for this problem.

You've chosen the gal who can cook but would crack a mirror.  I choose the
one who looks great but can't cook.  Neither is the superior choice.

"Should" in the sense you intended it only exists if there is a gal out
there who looks great and can cook well ... I don't see it in this case.

There are some problems where there is a "right" answer ... but I don't see
it in this case because there is complexity introduced no matter which
direction you go.

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