In a relational supertype/subtype structure where, for example, I have a supertype table entity with an entity_type column and a number of subtype tables, is there any way I can go about querying all entities with their full records, that is somehow joining to each of the subtype tables automatically?
So, with:
TABLE entity
-- entity_id (INT pk)
-- entity_type_id (INT fk)
TABLE entity_type
-- entity_type_id (INT pk)
-- name //Person, Building, Animal (TEXT)
TABLE person
-- entity_id (INT fk)
-- person_name (TEXT)
-- person_age (INT)
TABLE building
-- entity_id (INT fk)
-- age_built (INT)
etc.
what if I wanted to query all entities, and in my result set get all person-specific columns (person_name, etc.) if the record was a person and age_built, etc. if the record was a building? I thought about storing the subtype table names in the type table but understand you can't dynamically reference those like that.
Am I being an ignorant DB newb here or is this in any way possible without explicitly defining the join and doing a query for each subtype table?
I'm asking this because elsewhere in my DB I'm going to have a lot of references to an entity_id (that could be any kind of entity) and I don't want to run an initial query to just to check its type first.
Working in MySQL, no preference to engine.
