Re: SQL for Modeling Generalization Hierarchies

Taking out of context slightly:
Tom Kyte's viewpoint on this kind of thing I think is:

"sounds like a single table design to me. I do not factor out 1:1 optional
relationships unless a table is "really really wide" -- really wide being into 3 digits.

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.

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

The question was asking about subtype modeling, but the example the questioner posed wasn't really subtype. The info they were talking about was actually M:1 relationships most likely, but Tom's comments apply to the subtype situation you are discussing.

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

Read the link for yourself to see the full thread:

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:12678058160686 Received on Thu Jun 03 2004 - 04:08:21 CDT