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 :
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.
( 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...