Select all full records in a supertype/subtype SQL pattern

Question

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.

Answer 1

Don't do this unless you really know what you are doing (and then probably don't do it!). This fits into the entity attribute value anitpattern. Better to model out the entities which relate to each person/animal/building separately.

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2314483800346542969

Either that or you could consider having multiple fk columns from entity type to the linked entities. How many entities are you expecting to reference entity_type?