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.

Try reading Introduction to Database Systems, by CJ Date.

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