Re: boolean datatype ... wtf?

On Oct 2, 7:27 am, Paul Mansour <psmansour2..._at_yahoo.com> wrote:
> On Oct 1, 6:24 pm, Erwin <e.sm..._at_myonline.be> wrote:
>
>
>
>
>
> > On 1 okt, 23:29, Gene Wirchenko <ge..._at_ocis.net> wrote:
>
> > >      I do not want the exponential explosion if I have customers with
> > > boolean attributes.  In my client billing system, there are two:
> > > whether the client billing is PDF only and whether they pay by credit
> > > card transaction where we process a payment after a delay if we have
> > > not heard otherwise.
>
> > >      Why would I want to split the client table into four tables?
>
> > > [snip]
>
> > > Sincerely,
>
> > > Gene Wirchenko
>
> > OK.  I admit that on the sheer face of it, your argument seems right.
>
> > But I propose we get a look _beyond_ the "face of it".
>
> > If you speak about "whether client billing is PDF only", you are
> > already suggesting that "client billing" really isn't just a boolean.
> > In particular, you are suggesting that there really are other billing
> > modes than "PDF", and that there may exist clients who want "PDF +
> > postal mail", that there are other clients who want "postal mail
> > only", and (for the humorous part of it) "no billing at all".
>
> > I propose you consider a design with a relvar that simply records
> > "client wants PDF billing", another one that records "client wants
> > postal mail billing", a constraint "client must express at least one
> > billing mode".
>
> > With such a design, if newly arising technology allows for "novel
> > billing modes", then all you have to do to your database is add
> > another relvar, plus include that relvar in the union that is part of
> > the constraint.
>
> > Similar observations apply to "customer's preferred mode of payment".
>
> > When I was a "young Turk" in IT land, I formulated my own design
> > principle : "codes are poor, entities are rich".  What I meant by that
> > is that "A boolean is a piece of 'codified' information, and therefore
> > it doesn't convey much information after all.  A separate entity (ER
> > terminology, relational equivalent being 'a separate relvar' holding
> > particular tuples) allows for specifying WHY that boolean piece of
> > information happens to be true.".
>
> > IOW: if a boolean is true, then somewhere deeper down inside, _THERE
> > IS ALWAYS A REASON WHY_, and _KNOWING_ that 'reason why' is _ALWAYS
> > MORE AND BETTER_ than just 'knowing that it is so'.
>
> > Oh, and I am very much aware of the case where legislation does not
> > allow you to actually be 'aware' of 'the reason why'.
>
> Erwin, thanks for your response to my previous questions.  I was
> trying to get my head around why if it is a good idea to split a
> column with two potential values into two separate relvars, it would
> not also be a good idea to put a column with n potential values into n
> separate relvars, when, in your response to Gene Wirchenko, you
> suggest just such a design, on the grounds that it is then easy to add
> new values, by simply adding a new relvar.   What does such a design
> possibly get you over a single column (perhaps with a lookup table)
> with n possible values whether in the main entity table or off in its
> own table to avoid nulls? Furthermore it takes a schema change to add
> a new category, rather than simply adding a new row to a lookup
> table.  So now you are suggesting that not only boolean columns are
> bad design but even columns with 3 or more unique values. Is there a
> number (say 25?), where you just say to heck with it, I'll go to a
> single table?   I must be missing something.- Hide quoted text -
>
> - Show quoted text -

Erwin, I wrote above "I must be missing something" and I certainly was! It wasn't until reading Gene's response that I realized you were not specifiying unique values, but allowing for one or more values. I did a careless reading of your original post. Sorry about that. Received on Tue Oct 05 2010 - 02:57:07 CEST