Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
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
add comment

1 Answer 1

up vote 0 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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