2

The model of the (PHP & MySQL based) Zend Framework 2 application I’m currently working on contains a inheritance similar to the one described here.

To map this to the database I’m using the Class Table Inheritance design pattern.

I’m cheating a bit and let the “super-entity” (i.e., the supertype) know, which type of “sub-entity” (i.e., the subtype) belongs to it. That means, the “super-entity” has a property (i.e., attribute or column) named type. The value is camel-cased (for an easier usage in the application). The table names are always underscored.

Here is a diagram depicting my database structure

ERM/PDM

And a diagram displaying class models for my application program objects

class model

The problem is now the retrieving. When I want to get a Car with id=123, I don't know at first, which type it has and hence which car_* table I have to JOIN. Well, I could workaround it, either JOINing every time all car_* tables or making two request (when the first one is something like a “pre-request” and is used to get the type: SELECT type FROM car;), but I’m sure, there must be a better solution.

How to SELECT/JOIN a TABLE with a variable name, that equals to a selected value in another TABLE?

  • I don't think this is possible in any SQL RDBMS. You'll have to use two separate queries. – mustaccio May 6 '16 at 17:28
  • You mean my first workaround? Well it's not nice, but it would work. But only when I want to get one result (WHERE id = 123). For result sets it's not even a workaround. – automatix May 6 '16 at 18:13
1

You can't directly without drawing in all the tables. You would need to first pick out the table name needed then use ad-hoc SQL or call a specific procedure per table to get the data out.

Drawing in all the tables as follows would work:

SELECT ac.carID, ac.type, ac.commonAttribute1, ac.commonAttribute2
     , ct.specialAttributeA
     , ca.specialAttributeB
     , cb.specialAttributeC
FROM   AbstactCar ac
LEFT OUTER JOIN
       CarTesla ct ON ct.carID = ac.CarID
LEFT OUTER JOIN
       CarAstonMamn ca ON ca.carID = ac.CarID
LEFT OUTER JOIN
       CarBMW cb ON cb.carID = ac.CarID

but obviously this is limited by needing to know all the types in advance and/or update the query every time a type is added/dropped/changed and possibly hitting some limit on the number of tables in any given SELECT. If a non-common attribute can appear in more than on of the sub-classes then change the definition for that output column to something like ca.specialAttributeD, cb.specialAttributeD AS specialAttributeD.

NOTE: I'm pretty sure the above is mysql compatible, but I'm generally an MS SQL Server fellow and haven't tested it so you may need to tweak the syntax slightly.

  • Thank you for your answer! It's a workaround and not a solution. But there also seems not to be a real solution in MySQL (at least for now). Anyway thanks again for trying to help! – automatix May 6 '16 at 18:51
  • @automatix well then, I would say that only "solution" would be to use a different schema which is more suited for a relational DB. – jkavalik May 6 '16 at 19:27
  • No, there isn't anything that mySQL and its descendants can do for you currently. PostgreSQL natively supports a form of table inheritance (postgresql.org/docs/current/static/ddl-inherit.html) which, particularly when combined with partitioning, may do what you are looking for if you were able to use that instead of mySQL. – David Spillett May 8 '16 at 1:15

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.