Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2010
    Posts
    6

    Subtypes in a Single Table

    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.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by milkbag View Post
    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 View Post
    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 View Post
    ...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?).
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Apr 2010
    Posts
    6
    Employed? Not sure what you mean by that.
    Poor choice of words perhaps...i simply meant used.

    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.

    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.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by milkbag View Post
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Apr 2010
    Posts
    6
    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 )

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.

  8. #8
    Join Date
    May 2008
    Posts
    277
    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,
        ...
    );

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •