Subtypes in a Single Table

milkbag · #1 (**permalink**) 05-13-10, 13:51

Hi,

I am presently trying to make the call between employing a super/subtype design as described here...

Implementing Table Inheritance in SQL Server - SQLTeam.com

or collapsing the super/sub data into a single table. Based on the limited amount of data that will be particular to any subtype, the information provided in the countless forum postings I have read here over the last few days, and the anticipated increased development complexity of implementing the subtype pattern......I am leaning towards collapsing it into a single table.

My question is ... for those who have expressed disdain for the subtype pattern (blindman, i am talking to you) ...Given a single table design, are there any strategies that you would recommend to ensure that only columns relevant to a row’s ‘subtype’ are employed? (ex. In keeping with the example from the above referenced subtype article (assuming the subtypes are collapsed into a single Person table).....that a Person row of type Teacher, has a non-null hiredate, a null enrolment date and a null difficulty score). Is it simply a series of check constraints?

Thanks in advance.

blindman · #2 (**permalink**) 05-14-10, 10:43

Quote:

Originally Posted by milkbag

My question is ... for those who have expressed disdain for the subtype pattern (blindman, i am talking to you)

Did I just hear the Bat-phone ringing?

Quote:

Originally Posted by milkbag

Given a single table design, are there any strategies that you would recommend to ensure that only columns relevant to a row’s ‘subtype’ are employed?

Employed? Not sure what you mean by that. If you mean "visible to the user", then your application should only show relevent fields on its forms, or you could create views off of your single table to materialize the subtype/supertype model virtually, without all the messy relations that model requires.

Quote:

Originally Posted by milkbag

...that a Person row of type Teacher, has a non-null hiredate, a null enrolment date and a null difficulty score). Is it simply a series of check constraints?

Why would you care about enforcing this? Why can't a teacher have a non-null hiredate? Why can't a teacher have an enrollment date (someone can't be both a teacher and a student?).

milkbag · #3 (**permalink**) 05-14-10, 11:15

Quote:

Employed? Not sure what you mean by that.

Poor choice of words perhaps...i simply meant used.

Quote:

If you mean "visible to the user", then your application should only show relevent fields on its forms, or you could create views off of your single table to materialize the subtype/supertype model virtually, without all the messy relations that model requires.

Understood. But I was looking for any additional measures that you would take to ensure that subtype specific columns are not used for a subtype for which they are not relevant. One of the main arguments against a one true look up table (OTLT?) as I understand it is that a foreign key does not actually ensure that references are made 'correctly'. If for example, the OTLT contains several 'types' of codes, then a FK does not ensure that codes of type X are only applied to X.
In both cases, the DB would permit data to be entered that violates the intention of the model.

Quote:

Why would you care about enforcing this? Why can't a teacher have a non-null hiredate? Why can't a teacher have an enrollment date (someone can't be both a teacher and a student?).

Sorry I was unclear. I was just referencing the example used within the article I linked to....the assumption was that a person could not be both a teacher and a student; and that some attributes were particular to a subtype. For my project, the pattern is the same in that there will be some columns that will be only used for a particular subtype.

pootle flump · #4 (**permalink**) 05-14-10, 11:23

Yes.
The subtype design pattern is a means of ensuring that data only relevant to a specific type will be populated for that specific type and no other. To roll it all up into one table but retain this constraint you will effectively need to make your optional columns "conditionally NULL". You would do this with check constraints as you said.

There are though several combinations of NULLability to account for:
e.g.
* Teacher only, nulls allowed for teacher
* Teacher only, nulls not allowed for teacher

For the first, it must be NULL if type is not teacher, but can be NULL if type is teacher.
Second, it must be NULL if type is not teacher, and must not be NULL if type is teacher.

blindman · #5 (**permalink**) 05-14-10, 11:43

Quote:

Originally Posted by milkbag

I was looking for any additional measures that you would take to ensure that subtype specific columns are not used for a subtype for which they are not relevant.

Can you give a practical example of why you would care that a column is used for a subtype for which it is not relevant?

I think you are fretting about this too much.

milkbag · #6 (**permalink**) 05-14-10, 12:04

Quote:

Can you give a practical example of why you would care that a column is used for a subtype for which it is not relevant?

Just in the interest of data integrity....so that the DB does not allow data in a record where it does not logically belong. As a quick example, something like (where * denotes subtype specific fields)....

Product
id
productType (CD OR DVD OR BOOK)
title
price
(*)numberOfPages
(*)videoFormat

If you believe I am fretting too much, then maybe that's the answer

.... i should just get over it. I was just wondering if there were any best practices out there that one of you would recommend for this type of situation. (cue one of you to suggest dumping the consolidated table and move to a subtype pattern

)

pootle flump · #7 (**permalink**) 05-14-10, 12:23

I don't think you are fretting too much.

I think it should be constrained for practical logical reasons; data integrity as you say. Here's a practical physical reason - you don't want your data pages (and perhaps even indexes) stuffed with data you don't want or use.
Finally, using check constraints means your table is self documenting - a DBA in 5 years time will not be scratching his\ her head wondering why DVDs should have a number of pages entry.

futurity · #8 (**permalink**) 05-14-10, 14:55

Keep in mind that, if you collapse all the subtypes into one table, the subtype enforcement trick that the article uses will now need to be used wherever you need to enforce a relation to a specific subtype.

So, for example:

Code:

CREATE TABLE class_enrollment (
    ...
    student_id INT REFERENCES student,
    ...
);

becomes

Code:

CREATE TABLE class_enrollment (
    ...
    person_id INT,
    person_type VARCHAR DEFAULT 'Student' CHECK (person_type = 'Student'),
    FOREIGN KEY (person_id, person_type) REFERENCES person,
    ...
);

Internet Services

Web Development

Digital Marketing

Consumer Tech