Hi all,

I am reading J. Hoffer’s Database Management book and need your opinions on the following supertype/subtype relationship concept.

This book suggested using the different modified forms of Supertype’s Primary Key for Subtypes as foreign key. For example, if the supertype PK is ABC_ID, we should use
A_ABC_ID for subtype A
B_ABC_ID for subtype B
C_ABC_ID for subtype C

I made and uploaded a low-resolution image of related paragraphs and diagrams at the following address:

http://www.geocities.com/fellowreade...stionimage.htm
or
geocities.com/fellowreaderz/questionimage.htm

My question is:

1) When the subtypes have no overlap (like the example in the book), why can’t we use the unchanged supertype’s PK (ABC_ID) (without adding the discriminators) in all subtypes? Like ordinary one-to-one relationships?

Any help and comment would be appreciated.

Thanks
Sam
.

I would like to know this also. I just checked out the passage, and I cant see a reason why you would bother adding a discriminator to the PK for the subtype, as the subtype table is seperation enough.

After a little thought I might have the answer.

his SQL included the line:

WHERE employee_number = s_employee_number;

This is apposed to:

WHERE employee.employee_number = salaried_employee.employee_number;

I think the design approach maybe to simplify SQL queries.

Thanks jwab. You are right the SQL codes is shorter. But if this is the main reason, at least for me, using:

"WHERE employee.employee_number = salaried_employee.employee_number;"

is much easier than creating 3 new incremented keys with specific structure (Stringe+Supertype's PK)

I wonder if ignoring the book recommendation and using the ordinary one-to-one relationships (supertype's PK in subtypes as foreign key + their own PK) causes any problem.

By the way, I missed a paragraphy while creating the book image. I uploeded a corrected one at:

http://www.geocities.com/fellowreade...stionimage.htm

direct link to image
www.geocities.com/fellowreaderz/myquestion.gif

I look forward further ideas?

Thanks again
Sam
.

The confusion lies in his imprecise nomenclature. He merely is referring to adding a prefix to the attribute name of the sub table primary key (which refers to the pk of the supertype table) for, as was noted, a slight change in the SQL.

For example, he proposes (SQL shorthand):
CREATE TABLE subtype( s_item_id REFERS supertype( item_id ) )

instead of
CREATE TABLE subtype( item_id REFERS supertype( item_id ) )

You could just as easily do:
CREATE TABLE subtype( subtype_id REFERS supertype( item_id ) )

or anything else.

He's also incorrect in saying that the relational model does not "support" entity sub/supertypes. That is not surprising.

Thanks a lot Matt.
Now, I am more comfortable to try other ways.

Cheers
Sam