Is-a relation(ERM) to tables

database-designrelational-theory

i try to design a database with an is-a relation:

Vehicles have some general attributes and the SubVehiclesX have some specialized Attributes.

SubvehicleX is a Vehicle

And a Person has a Vehicle

In the VehicleNames-Table is e.g.:

ID | TableName

1 | SubVehicle1
:

is - a relation

Now I want to find out all Attributes of the Vehicle

  SELECT * 
  FROM VEHICLE v
  LEFT JOIN (
             SELECT vn.TableName  
             FROM VehicleNames vn 
             WHERE vn.ID = (SELECT NameOfVehicle 
                        FROM Vehicle v  
                        WHERE v.ID = VARIABLE1)
            ) x on x.ID = v.Vehicle
  WHERE v.ID = VARIABLE1

I want it as dynamic as possible, so when i want a new subvehicle i only must insert a row in the VehicleNames and create a new table. I haven't to change my program code.

the part:

            (
             SELECT vn.TableName  
             FROM VehicleNames vn 
             WHERE vn.ID = (SELECT NameOfVehicle 
                        FROM Vehicle v  
                        WHERE v.ID = VARIABLE1)
            )

must be handled in the Programm code or is there an function to take the Result as a tablename?? Left Join SubVehicle1 x

Is there any other alternative to this? I find this Version isn't clean…

Best Answer

Denormalization to the 'Vehicle' table is still option, technically, but based on the responses in the comments I would probably sub-class it as well. So, you're at the options I mentioned in my last comment. There's no magic bullet. It's string concatenation for dynamic SQL or separate statements. One way or another you're going to have to change your app code.

Related Question