Re: SQL for Modeling Generalization Hierarchies

From: Noons <wizofoz2k_at_yahoo.com.au.nospam>
Date: Thu, 03 Jun 2004 19:32:18 +1000
Message-ID: <40bef01b$0$8985$afc38c87_at_news.optusnet.com.au>


Job Miller allegedly said,on my timestamp of 3/06/2004 7:08 PM:

> put the most likely to be null columns at the end of the create table
> and when
> they are NULL, they will consume NO space.
>

The problem is when you may have two or more of these optional 1:1 relationships. What happens when you got a bunch of columns set to NULL, another bunch set to values, then another bunch set to NULL again, and someone comes along and fills the first bunch with values?

> benchmark it. you would always have to join to pick up this optional
> information (2 or 3 LIO's at least per row retrieved for each optional
> set of
> data) vs an extra 50 bytes of flags saying "this is null". I would go
> for the
> extra 50 bytes in a row that will be accessed via an index rather then
> incurring 2/3 LIO's to read an index to access another table. "

I wouldn't. If it made the app easier to document, code and expand, I'd trade the extra 2 or 3 lios easily. Heck, I'd trade 10 or more lios easily, for that! That is the sort of price I don't mind paying.

Assuming of course I'm not designing/writing the next 10000000/TPS TPC benchmark.
Then again, how many of those has anyone found in real life? Then again, how many of those do indeed need a 1:1? It's all relative, no?

In general, designs calling for that sort of level of specification are not for performance critical apps, which makes the extra lio quite acceptable.

> Your model and application are much more simple with this single table
> model.

I don't think so. I have to add a heap of IS [NOT] NULL combinations to handle existence or not existence of subtypes. IS NULL is not an easy condition to combine with others. And it makes the code unnecessarily dense, as well as stopping me from ever being able to run this app in a db that doesn't support NULLS the way Oracle does. In all, not a good choice. I'm afraid on that one I'll have to disagree with Tom, assuming once again we are not talking about a TPC benchmark type of app.

-- 
Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam
Received on Thu Jun 03 2004 - 11:32:18 CEST

Original text of this message