0

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

  • Why are you creating a new table for every 'subvehicle'? – Jon Boulineau Nov 11 '13 at 18:06
  • The Subvehicles are like car, motorcycle, boat. When i have 5 cars i don't create 5 tables, i insert them. But when i need a new "class" like a truck i create a new Table – Jackson Nov 11 '13 at 18:12
  • I figured ... but the question remains, why do you do that? It may or may not be necessary or desirable. Assuming you have a good reason, you're looking at using either dynamic SQL or writing separate statements for each subtype and choosing which one to execute in the app. The later is generally wiser. – Jon Boulineau Nov 11 '13 at 18:20
  • Because a boat has other properties than a truck. But some same, like the color or the length. And I don't want ONE vehicle table with many useless attributes with a "null"-value( e.g. wheels by a boat) and a table with the vehicle type. – Jackson Nov 11 '13 at 18:43
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.