Subtyping database tables

Question

I hear a lot about subtyping tables when designing a database, and I'm fully aware of the theory behind them. However, I have never actually seen table subtyping in action. How can you create subtypes of tables? I am using MS Access, and I'm looking for a way of doing it in SQL as well as through the GUI (Access 2003).

Cheers!

Altheta

Answer 1

An easy example would be to have a Person table with a primary key and some columns in that table. Now you can create another table called Student that has a foreign key to the person table (its supertype). Now the student table has some columns which the supertype doesn't have like GPA, Major, etc. But the name, last name and such would be in the parent table. You can always access the student name back in the Person table through the foreign key in the Student table.

Anyways, just remember the following:

• The hierarchy depicts relationship between supertypes and subtypes
• Supertypes has common attributes
• Subtypes have uniques attributes

Fount

Sorry but I think you are merely describing a REFERENCE. Your so-called supertype table lacks a type attribute! For a better IMO example see the CREATE TABLE Vehicles post in this thread: bytes.com/groups/ms-sql/… – Ricercare

Yes, you are talking about a discriminator column, which is another approach to solve the same problem. I was well aware of that approach. That doesn't mean my solution is incorrect. I just wanted to give a brief SIMPLE example. In the link you posted, the post clearly states that there are many ways to model such structures. A similar example to the one I posted is mentioned in the book "Database Systems: Design, Implementation and Management 6th ed." p. 150, 151 and 159. – Fount

My quibble with the above is that you should have made more explicit that the PK of the Student table has a Foreign Key constraint on the Surrogate Key of the Person table. The import of this is that the tuples of the Student relation have no identity independent of the corresponding tuples in the Person table; hence, this is a true sub-type relation. It is NOT a reference, as onedaywhen claims. – Proglottis

Answer 2

Subtypes of tables is a conceptual thing in EER diagrams. I haven't seen an RDBMS (excluding object-relational DBMSs) that supports it directly. They are usually implemented in either

1. A set of nullable columns for each property of the subtype in a single table
2. With a table for base type properties and some other tables with at most one row per base table that will contain subtype properties

Kirstenkirsteni

Answer 3

The notion of table sub-types is useful when using an ORM mapper to produce class sub-type heirarchy that exactly models the domain.

A sub-type table will have both a Foreign Key back to its parent which is also the sub-types table's primary key.

Nahshu

If the subtype table records have a 1:1 relationship to the parent table, why would they not just use the FK as PK? – Luncheonette

Answer 4

Keep in mind that in designing a bound application, as with an Access application, subtypes impose a heavy cost in terms of joins.

For instance, if you have a supertype table with three subtype tables and you need to display all three in a single form at once (and you need to show not just the supertype date), you end up with a choice of using three outer joins and Nz(), or you need a UNION ALL of three mutually exclusive SELECT statements (one for each subtype). Neither of these will be editable.

I was going to paste some SQL from the first major app where I worked with super/subtype tables, but looking at it, the SQL is so complicated it would just confuse people. That's not so much because my app was complicated, but it's because the nature of the problem is complex -- presenting the full set of data to the user, both super- and subtypes, is by its very nature complex. My conclusion from working with it was that I'd have been better off with only one subtype table.

That's not to say it's not useful in some circumstances, just that Access's bound forms don't necessarily make it easy to present this data to the user.

Luncheonette

Answer 5

I have a similar problem I've been working on.

While looking for a repeatable pattern, I wanted to make sure I didn't abandon referential integrity, which meant that I wouldn't use a (TABLE_NAME, PK_ID) solution.

I finally settled on:

Base Type Table: CUSTOMER

Sub Type Tables: PERSON, BUSINESS, GOVT_ENTITY

I put nullable PRERSON_ID, BUSINESS_ID and GOVT_ENTITY_ID fields in CUSTOMER, with foreign keys on each, and a check constraint that only one is not null. It's easy to add new sub types, just need to add the nullable foreign key and modify the check constraint.

Amusement