Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2006
    Posts
    43

    Multiple Subtypes

    The scenario is a Person table with multiple subtables, such as Employee, Musician, Athlete and other roles that describe the Person. Any given Person record may be associated with 0 to many subtypes (i.e. one Person can be just a Person, while another Person may be an Employee and a Musician.)

    If needing to extract all information related to a given Person, how would the query look? A UNION query with separate queries that look at all 4 of Person, Employee, Musician, and Athlete wouldn't even work, because each subtable has different columns. And having 4 independent queries seems like such an ugly solution, because the query would have to be changed for each subtable that is added or removed. On top of that, I see an inefficiency issue:

    For a given Person that has no subtypes, would all 4 queries still need to be done? (I'd imagine so because there's no way of knowing with which subtypes a given Person record is associated without doing the join to each subtable, right? A discriminator cannot even be used to choose which query to execute because there are multiple subtypes.) I can see this becoming a big performance hit if Person has far more subtypes, and an unnecessary performance hit for a given Person record that has no subtypes!

    How would you go about structuring the above query, and what performance implications would it have? Does it deal elegantly with Person records that have no or few subtype records? Wow...is any of this even possible?!

    Your help on this would be appreciated. Thanks.

  2. #2
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    If needing to extract all information related to a given Person, how would the query look?
    Code:
    SELECT p.*, m.*, e.* ...
    FROM person p LEFT OUTER JOIN musician m on p.id = m.id
    LEFT OUTER JOIN employee e on p.id = e.id
    ...
    Wow...is any of this even possible?!
    Try reading Introduction to Database Systems, by CJ Date.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sco08y, that's an interesting query

    the result set will surely contain a whacking great number of NULLs, right? and the application is going to have to examine the m.* fields and detect when they are NULL to conclude that the person isn't a musician, and then examine the e.* fields and detect when they are NULL to conclude that the person isn't an employee, etc.

    so, what is the difference between the application processing that query's result set, compared with the application processing a single table which includes all the necessary columns, many of which will be NULL?

    well, fewer columns in the result set, for starters

    anyhow, i tend to implement the supertype/subtype structure as a single table

    i have yet to find a scenario where i ~need~ to split the subtypes off into separate tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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