Re: SQL for Modeling Generalization Hierarchies
Job Miller allegedly said,on my timestamp of 3/06/2004 7:08 PM:
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?
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.
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@yahoo .com.au.nospam
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.
>
and when
they are NULL, they will consume NO space.
>
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. "
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. "
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.
model.
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@yahoo .com.au.nospam
Comment