473,324 Members | 1,856 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

SQL for Modeling Generalization Hierarchies

Is there a good approach to modelling many heterogeneous entity types
with that have some attributes in common?

Say I have entities "employees" which share some attibutes (e.g.
firstname, lastname, dateofbirth) but some subsets of employees (e.g.
physicians, janitors, nurses, ambulance drivers) may have additional
attributes that do not apply to all employees. Physicians may have
attributes specialty and date of board certification, ambulance
drivers may have a drivers license id, janitors may have
preferredbroomtype and so on.

There are many employee subtypes and more can be dynamically added
after the application is deployed so it's obviously no good to keep
adding attributes to the employees table because most attributes will
be NULL (since janitors are never doctors at the same time).

The only solution I found for this is a generalization hiearchy where
you have the employee table with all generic attributes and then you
add tables for each new employee subtype as necessary. The subtype
tables share the primary key of the employee table. The employee table
has a "discriminator" field that allows you to figure out which
subtype table to load for a particular entity.

This solution does not seem to scale since for each value of
"discriminator" I need to perform a join with a different table. What
if I need to retrieve 1,000 employees at once?

Is that possible to obtain a single ResultSet with one SQL statement
SQL?

Or do you I need to iterate look at the discriminator and then
perform the appropriate join? If this kind of iteration is necessary
then obviously this generalization hierarchy approach does not work in
practice
since it would be painfully slow.

Is there a better approach to modelling these kind of heterogeneous
entities with shared attributes that does not involve creating a table
for each new employee type or having sparce tables (mostly filled with
NULLS)

I guess another approach would be to use name/value pairs but that
would make reporting really ugly.

Seems like a very common problem. Any ideas? Is this a fundamental
limitation of SQL?

Thanks!

- robert
Jul 20 '05 #1
13 2143

"Robert Brown" <ro*************@yahoo.com> wrote in message
news:24**************************@posting.google.c om...
Is there a good approach to modelling many heterogeneous entity types
with that have some attributes in common?
This is a frequently asked question. Unfortunately, the frequently given
responses don't usually settle the matter.

There are many employee subtypes and more can be dynamically added
after the application is deployed so it's obviously no good to keep
adding attributes to the employees table because most attributes will
be NULL (since janitors are never doctors at the same time).

The only solution I found for this is a generalization hiearchy where
you have the employee table with all generic attributes and then you
add tables for each new employee subtype as necessary. The subtype
tables share the primary key of the employee table. The employee table
has a "discriminator" field that allows you to figure out which
subtype table to load for a particular entity.
If you invent new subtypes on an adhoc basis, (e.g. a DBA is never either a
janitor or a doctor)
and you invent new tables for subtypes on an equally ad hoc basis, I'd
suggest that you are altering your data model on an ad hoc basis.

You can do that if you want, but don't expect the same power and simplicity
that you get from a stable data model.

This solution does not seem to scale since for each value of
"discriminator" I need to perform a join with a different table. What
if I need to retrieve 1,000 employees at once?
Why would you ever need the attributes of more than one subtype? If your
query is about doctors, why would you need to join the table about janitors
into the query?

If your query is about employees, why would you need to know any of the
subtype attributes?

Or do you I need to iterate look at the discriminator and then
perform the appropriate join? If this kind of iteration is necessary
then obviously this generalization hierarchy approach does not work in
practice
since it would be painfully slow.
You need to ask two questions about the model, and about any imlpementation
based on the model:
first, is it correct and complete? Second, if yes to the first, is it fast
enough?

In general, different implementations of the same model should be
transparent to users of the imlpementation who only look at the features
visible in the model.


Is there a better approach to modelling these kind of heterogeneous
entities with shared attributes that does not involve creating a table
for each new employee type or having sparce tables (mostly filled with
NULLS)
If you have new entities, you are going to have new relations. That either
means having new tables, or fudging the relationship between tables and
rleations. Do the second one at your own peril.
Seems like a very common problem. Any ideas? Is this a fundamental
limitation of SQL?


I would suggest it goes beyond SQl to the very heart of using foreign key/
primary key aossciations to establish linkages.
Jul 20 '05 #2

"Robert Brown" <ro*************@yahoo.com> wrote in message
news:24**************************@posting.google.c om...
adding attributes to the employees table because most attributes will
be NULL (since janitors are never doctors at the same time).


By the way, after I wrote "a DBA is never a janitor or a doctor" it
occurred to me that when I was a DBA there were days when I felt like I was
both at the same time! ;)

Jul 20 '05 #3
Robert Brown (ro*************@yahoo.com) writes:
The only solution I found for this is a generalization hiearchy where
you have the employee table with all generic attributes and then you
add tables for each new employee subtype as necessary. The subtype
tables share the primary key of the employee table. The employee table
has a "discriminator" field that allows you to figure out which
subtype table to load for a particular entity.

This solution does not seem to scale since for each value of
"discriminator" I need to perform a join with a different table. What
if I need to retrieve 1,000 employees at once?
So what? 1000 rows for a modern RDBMS is a breeze.
Is that possible to obtain a single ResultSet with one SQL statement
SQL?
Yes, although with many discriminators, it will be one hell of a join:

SELECT main.col1, main.col2, ... d1.col1, d1.col2, ...
FROM main
LEFT JOIN discriminator d1 ON d1.keycol = main.keycol
LEFT JOIN discriminator d2 ON d2.keycol = main.keycol
...

In practice, things might be even messier, because some values might
apply to four discriminators, but be irrelevant to the rest. In
they the would be four different columns. Although, this could be
addressed with:

common_to_four = colaesce(d3.common, d6.common, d9.common, d11.common)

But it may be better to add this as a nullable column to the common
table.

Likewise, if two discrimiators are very similar, it may be better to
lump them in the same table.
I guess another approach would be to use name/value pairs but that
would make reporting really ugly.
And you get less control over your spelling errors. But sometimes this
is the way to go.
Seems like a very common problem. Any ideas? Is this a fundamental
limitation of SQL?


Sort of. SQL tables are squared, and object-oriented hierachies are
jagged.

But that's alright, just don't be too object-oriented. Be pragmatic too.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4
"Laconic2" <la******@comcast.net> wrote in message news:<E8********************@comcast.com>...
"Robert Brown" <ro*************@yahoo.com> wrote in message
news:24**************************@posting.google.c om...
Is there a good approach to modelling many heterogeneous entity types
with that have some attributes in common?
This is a frequently asked question. Unfortunately, the frequently given
responses don't usually settle the matter.


Could you please post links to any relevant past threads if you
remember where to find them?I tried to search the news group for
"generalization hierarchy" but did not find anything useful. Is there
a better search term I can use?
There are many employee subtypes and more can be dynamically added
after the application is deployed so it's obviously no good to keep
adding attributes to the employees table because most attributes will
be NULL (since janitors are never doctors at the same time).

The only solution I found for this is a generalization hiearchy where
you have the employee table with all generic attributes and then you
add tables for each new employee subtype as necessary. The subtype
tables share the primary key of the employee table. The employee table
has a "discriminator" field that allows you to figure out which
subtype table to load for a particular entity.
If you invent new subtypes on an adhoc basis, (e.g. a DBA is never either a
janitor or a doctor)
and you invent new tables for subtypes on an equally ad hoc basis, I'd
suggest that you are altering your data model on an ad hoc basis.

You can do that if you want, but don't expect the same power and simplicity
that you get from a stable data model.

This solution does not seem to scale since for each value of
"discriminator" I need to perform a join with a different table. What
if I need to retrieve 1,000 employees at once?


Why would you ever need the attributes of more than one subtype? If your
query is about doctors, why would you need to join the table about janitors
into the query?


Because I need to display users and their heterogenious attributes in
the UI as one list (e.g. in response to a search).
If your query is about employees, why would you need to know any of the
subtype attributes?


Same reason, our customer's HR department does not see the employees
as separate entities. To them an employee is an employee and they want
to see all the associated data while browsing employees. I guess you
could show the generic attributes on the first pass and then require
the end user to click to drill down and see the subtype attributes -
but that's not what they want.

Or do you I need to iterate look at the discriminator and then
perform the appropriate join? If this kind of iteration is necessary
then obviously this generalization hierarchy approach does not work in
practice
since it would be painfully slow.


You need to ask two questions about the model, and about any imlpementation
based on the model:
first, is it correct and complete? Second, if yes to the first, is it fast
enough?

In general, different implementations of the same model should be
transparent to users of the imlpementation who only look at the features
visible in the model.


Is there a better approach to modelling these kind of heterogeneous
entities with shared attributes that does not involve creating a table
for each new employee type or having sparce tables (mostly filled with
NULLS)


If you have new entities, you are going to have new relations. That either
means having new tables, or fudging the relationship between tables and
rleations. Do the second one at your own peril.
Seems like a very common problem. Any ideas? Is this a fundamental
limitation of SQL?


I would suggest it goes beyond SQl to the very heart of using foreign key/
primary key aossciations to establish linkages.

Jul 20 '05 #5
In the last exciting episode, "Laconic2" <la******@comcast.net> wrote:
"Robert Brown" <ro*************@yahoo.com> wrote in message
news:24**************************@posting.google.c om...
adding attributes to the employees table because most attributes will
be NULL (since janitors are never doctors at the same time).


By the way, after I wrote "a DBA is never a janitor or a doctor" it
occurred to me that when I was a DBA there were days when I felt
like I was both at the same time! ;)


Some days it also combines coroner and roto-rooter operator...

A couple coworkers were in that state today :-(.
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','acm.org').
http://www3.sympatico.ca/cbbrowne/advocacy.html
If a person with multiple personalities threatens suicide, is that
considered a hostage situation?
Jul 20 '05 #6
This is always messy. With dynamically added subtypes, I would
consider keeping everything soft. Here is a first approximation for
the tables:

Employee Valid Subtypes Attributes/Subtype Valid Attributes
Subtype<--------Subtype--------->Subtype ?Datatype
Employee # ?Desc Attribute<-----------Attribute
| ?Desc
|
Employee Attributes
Employee #
Attribute
Attribute data

You could then build a list of available attributes for an employee
based on his subtype from the attributes/subtype table. The data
entry screen(s) could be built from the Valid Attributes
table(depending on how smart it needs to be).

This is a bit rough, but I hope the idea comes through. Control over
the Valid Subtypes table, the Attributes table, etc. would need to be
defined (who and how), but, gosh, I would have to have to create a new
table every time a new subtype was added.

LOL.. here I go again, ask me what time it is and I tell you how to
build a watch. Hope this helps more than it hurts.

Payson
ro*************@yahoo.com (Robert Brown) wrote in message news:<24**************************@posting.google. com>...
Is there a good approach to modelling many heterogeneous entity types
with that have some attributes in common?

Say I have entities "employees" which share some attibutes (e.g.
firstname, lastname, dateofbirth) but some subsets of employees (e.g.
physicians, janitors, nurses, ambulance drivers) may have additional
attributes that do not apply to all employees. Physicians may have
attributes specialty and date of board certification, ambulance
drivers may have a drivers license id, janitors may have
preferredbroomtype and so on.

There are many employee subtypes and more can be dynamically added
after the application is deployed so it's obviously no good to keep
adding attributes to the employees table because most attributes will
be NULL (since janitors are never doctors at the same time).

The only solution I found for this is a generalization hiearchy where
you have the employee table with all generic attributes and then you
add tables for each new employee subtype as necessary. The subtype
tables share the primary key of the employee table. The employee table
has a "discriminator" field that allows you to figure out which
subtype table to load for a particular entity.

This solution does not seem to scale since for each value of
"discriminator" I need to perform a join with a different table. What
if I need to retrieve 1,000 employees at once?

Is that possible to obtain a single ResultSet with one SQL statement
SQL?

Or do you I need to iterate look at the discriminator and then
perform the appropriate join? If this kind of iteration is necessary
then obviously this generalization hierarchy approach does not work in
practice
since it would be painfully slow.

Is there a better approach to modelling these kind of heterogeneous
entities with shared attributes that does not involve creating a table
for each new employee type or having sparce tables (mostly filled with
NULLS)

I guess another approach would be to use name/value pairs but that
would make reporting really ugly.

Seems like a very common problem. Any ideas? Is this a fundamental
limitation of SQL?

Thanks!

- robert

Jul 20 '05 #7
>> Is there a good approach to modelling many heterogeneous entity
types with that have some attributes in common? <<

Ignoring that the phrase "entity type" is OO **yuck!**

The classic scenario calls for a root class with all the common
attributes and then specialized sub-classes under it. As an example,
let's take the class of Vehicles and find an industry standard
identifier (VIN), and add two mutually exclusive sub-classes, Sport
utility vehicles and sedans ('SUV', 'SED').

CREATE TABLE Vehicles
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) NOT NULL
CHECK(vehicle_type IN ('SUV', 'SED')),
UNIQUE (vin, vehicle_type),
..);

Notice the overlapping candidate keys. I then use a compound candidate
key (vin, vehicle_type) and a constraint in each sub-class table to
assure that the vehicle_type is locked and agrees with the Vehicles
table. Add some DRI actions and you are done:

CREATE TABLE SUV
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT 'SUV' NOT NULL
CHECK(vehicle_type = 'SUV'),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
REFERENCES Vehicles(vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);

CREATE TABLE Sedans
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT 'SED' NOT NULL
CHECK(vehicle_type = 'SED'),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
REFERENCES Vehicles(vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);

I can continue to build a hierarchy like this. For example, if I had
a Sedans table that broke down into two-door and four-door sedans, I
could a schema like this:

CREATE TABLE Sedans
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT 'SED' NOT NULL
CHECK(vehicle_type IN ('2DR', '4DR', 'SED')),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
REFERENCES Vehicles(vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);

CREATE TABLE TwoDoor
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT '2DR' NOT NULL
CHECK(vehicle_type = '2DR'),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
REFERENCES Sedans(vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);

CREATE TABLE FourDoor
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT '4DR' NOT NULL
CHECK(vehicle_type = '4DR'),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
REFERENCES Sedans (vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);

The idea is to build a chain of identifiers and types in a UNIQUE()
constraint that go up the tree when you use a REFERENCES constraint.
Obviously, you can do variants of this trick to get different class
structures.

If an entity doesn't have to be exclusively one subtype, you play with
the root of the class hierarchy:

CREATE TABLE Vehicles
(vin CHAR(17) NOT NULL,
vehicle_type CHAR(3) NOT NULL
CHECK(vehicle_type IN ('SUV', 'SED')),
PRIMARY KEY (vin, vehicle_type),
..);

Now start hiding all this stuff in VIEWs immediately and add an
INSTEAD OF trigger to those VIEWs.

Here is the link on Amazon.com for my new book on "Trees & Hierarchies
in SQL"

http://www.amazon.com/exec/obidos/tg...roduct-details
Jul 20 '05 #8
--CELKO-- (jc*******@earthlink.net) writes:
CREATE TABLE Vehicles
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) NOT NULL
CHECK(vehicle_type IN ('SUV', 'SED')),
UNIQUE (vin, vehicle_type),
..);

Notice the overlapping candidate keys. I then use a compound candidate
key (vin, vehicle_type) and a constraint in each sub-class table to
assure that the vehicle_type is locked and agrees with the Vehicles
table. Add some DRI actions and you are done:


Ah! Interesting trick! I'll save a mental note about that.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #9
jc*******@earthlink.net (--CELKO--) wrote in message news:<18**************************@posting.google. com>...
Is there a good approach to modelling many heterogeneous entity
types with that have some attributes in common? <<

Ignoring that the phrase "entity type" is OO **yuck!**

The classic scenario calls for a root class with all the common
attributes and then specialized sub-classes under it. As an example,
let's take the class of Vehicles and find an industry standard
identifier (VIN), and add two mutually exclusive sub-classes, Sport
utility vehicles and sedans ('SUV', 'SED').

CREATE TABLE Vehicles
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) NOT NULL
CHECK(vehicle_type IN ('SUV', 'SED')),
UNIQUE (vin, vehicle_type),
..);

Notice the overlapping candidate keys. I then use a compound candidate
key (vin, vehicle_type) and a constraint in each sub-class table to
assure that the vehicle_type is locked and agrees with the Vehicles
table. Add some DRI actions and you are done:

CREATE TABLE SUV
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT 'SUV' NOT NULL
CHECK(vehicle_type = 'SUV'),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
REFERENCES Vehicles(vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);

CREATE TABLE Sedans
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT 'SED' NOT NULL
CHECK(vehicle_type = 'SED'),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
REFERENCES Vehicles(vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);

I can continue to build a hierarchy like this. For example, if I had
a Sedans table that broke down into two-door and four-door sedans, I
could a schema like this:

CREATE TABLE Sedans
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT 'SED' NOT NULL
CHECK(vehicle_type IN ('2DR', '4DR', 'SED')),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
REFERENCES Vehicles(vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);

CREATE TABLE TwoDoor
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT '2DR' NOT NULL
CHECK(vehicle_type = '2DR'),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
REFERENCES Sedans(vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);

CREATE TABLE FourDoor
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT '4DR' NOT NULL
CHECK(vehicle_type = '4DR'),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
REFERENCES Sedans (vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);

The idea is to build a chain of identifiers and types in a UNIQUE()
constraint that go up the tree when you use a REFERENCES constraint.
Obviously, you can do variants of this trick to get different class
structures.

If an entity doesn't have to be exclusively one subtype, you play with
the root of the class hierarchy:

CREATE TABLE Vehicles
(vin CHAR(17) NOT NULL,
vehicle_type CHAR(3) NOT NULL
CHECK(vehicle_type IN ('SUV', 'SED')),
PRIMARY KEY (vin, vehicle_type),
..);

Now start hiding all this stuff in VIEWs immediately and add an
INSTEAD OF trigger to those VIEWs.

Here is the link on Amazon.com for my new book on "Trees & Hierarchies
in SQL"

http://www.amazon.com/exec/obidos/tg...roduct-details


Good stuff, for one thing I don't get it,
Why would one need NOT NULL in the following DDL, CREATE TABLE Sedans
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT 'SED' NOT NULL
CHECK(vehicle_type IN ('2DR', '4DR', 'SED')),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
REFERENCES Vehicles(vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);

since by definition PRIMARY KEY cannot be null, so, why would I tell
people, "I'm an American and an American citizen", instead of just
say, "I'm an American"?
Jul 20 '05 #10
da**@rock.com (DaaaDaaa) wrote in message news:<21*************************@posting.google.c om>...

Good stuff, for one thing I don't get it,
Why would one need NOT NULL in the following DDL,
CREATE TABLE Sedans
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT 'SED' NOT NULL
CHECK(vehicle_type IN ('2DR', '4DR', 'SED')),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
REFERENCES Vehicles(vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);

Methinks he means theoretically the primary key could be separate from
the vin definition. Just because Oracle disallows it doesn't mean it
is redundant. I don't know about ANSI or other db's offhand.
since by definition PRIMARY KEY cannot be null, so, why would I tell
people, "I'm an American and an American citizen", instead of just
say, "I'm an American"?


'Cause you are not these guys:
http://www.signonsandiego.com/news/m...m11deport.html
http://www.signonsandiego.com/uniont...n29deport.html

Obviously, the attributes of American are cultural, and of a citizen
are legal. A citizen could be raised abroad and not consider
themselves American, and it is possible to have null citizenship.
http://www.keepmedia.com/ShowItemDet...0032&oliID=213
http://www.hrw.org/reports/1995/Kuwait.htm

jg
--
@home.com is bogus.
Happy Birthday, Jerry Mathers.
Jul 20 '05 #11
>> since by definition PRIMARY KEY cannot be null, so, why would I tell
people, "I'm an American and an American citizen", instead of just say,
"I'm an American"? <<

It is just a personal style. I design the columns before picking my
keys, so I need to know this while I am working. I like the bit of
redundancy in case the schema is moved without constraints in the table
declarations or if you have to change it from PRIMARY KEY to UNIQUE
later.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #12
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...12678058160686
Jul 20 '05 #13
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
wi*******@yahoo.com.au.nospam
Jul 20 '05 #14

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

15
by: Robert Brown | last post by:
Is there a good approach to modelling many heterogeneous entity types with that have some attributes in common? Say I have entities "employees" which share some attibutes (e.g. firstname,...
15
by: Robert Brown | last post by:
Is there a good approach to modelling many heterogeneous entity types with that have some attributes in common? Say I have entities "employees" which share some attibutes (e.g. firstname,...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.