CODEWITHSUNDEEP
mysqlsqlinheritance
Stezy
Stezy

Reputation: 390

SQL Inheritance, get by ID

This question aims to get the most clean and "best" way to handle this kind of problem.

I've read many questions about how to handle inheritance in SQL and like the Table Per Type model most and would like to use it. The problem with this is that you have to know what type you are going to query to do the proper join.

Let's say we have three tables:Son, Daughter and Child.

Database Model

This works very well if you for example want to query all daughters. You can simply join the child and get all the information.

What I'm trying to do is to query a Child by ID and get the associated sub class information. What I could do is to add a column Type to the child and select the associated data with a second select, but that does not seem pretty nice. Another way to do it would be to join all sub tables, but that doesn't seem to be that nice either.

Is there an inheritance model to solve this kind of problem in a clean, nice and performant way?

I'm using MySQL btw

Upvotes: 1

Views: 1520

Answers (1)

EagleRainbow
EagleRainbow

Reputation: 951

Given your detailed definition in the comment with the use case

The Server gets the http request domain.com/randomID.

it becomes apparent, that you have a single ID at hand for which you want to retrieve the attributes of derived entities. For your case, I would recommend to use the LEFT JOIN approach:

SELECT age, 
  son.id is not null as isSon,
  randomColumn,
  daughter is not null as isDaughter,
  whatEver

FROM child
  LEFT JOIN son on child.id = son.id
  LETT JOIN daughter on child.id = daughter.id

WHERE
  child.id = @yourRandomId

This approach, BTW, stays very close to your current database design and thus you would not have to change much. Yet, you are able to benefit from the storage savings that the improved data model provides.

Besides that, I do not see many chances to do it differently:

  • You have different columns with different datatypes (esp. if looking at your use case), so it is not possible to reduce the number of columns by combining some of them.
  • Introducing a type attribute is already rejected in your question; sending single SELECT statements as well.

In the comment you are stating that you are looking for something like Map<ID, Child> in MySQL. Please note that this java'ish expression is a compile-time expression which gets instantiated during runtime with the corresponding type of the instance. SQL does not know the difference between runtime and compile-time. Thus, there is also no need for such a generic expression. Finally, also please note that in case of your Java program, you also need to analyse (by introspection or usage of instanceof) which type your value instance has -- and that is also a "single-record" activity which you need to perform.

Upvotes: 3

Related Questions