From: Peter Brawley
Date: November 14 2008 6:12pm
Subject: Re: normalised designs: customer database
List-Archive: http://lists.mysql.com/mysql/215221
Message-Id: <491DBF9B.6050902@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------070203050102020007000602"

--------------070203050102020007000602
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Jujitsu Lizard wrote:

 >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

In O-O databases. I think the concept is not defined in relational 
database theory. Are you aware of the rel db rule regarding domains?

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

Relational theory says otherwise.

PB

-----

Jujitsu Lizard wrote:
> 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.
>
>   
> ------------------------------------------------------------------------
>
>
> Internal Virus Database is out of date.
> Checked by AVG - http://www.avg.com 
> Version: 8.0.175 / Virus Database: 270.9.0/1777 - Release Date: 11/9/2008 9:53 AM
>
>   

--------------070203050102020007000602--