How to handle inheritance in a relational database

Question

I am having some difficulty in understanding how inheritance works with relational databases.

Suppose I have the following classes:

public class Train {
    private int id;
    private String callsign;
    private List<Component> components = new ArrayList<Component>();

    public Train(){}

    public Train(String callsign){
        this.callsign = callsign;
    }
    ... getters & setters ...  
}

public abstract class Component {
    private int id;

    private String callsign;


    private Train train;

    public Component() {
    }

    public Component(String callsign) {
        this.callsign = callsign;
    }

    ... getters & setters ...  

}

public class Wagon extends Component{
    private int numOfSeats;

    public Wagon(String callsign, int numOfSeats) {
        super(callsign);
        this.numOfSeats = numOfSeats;
    }

    public Wagon() {
    }
    ... getters & setters ...  
}

A Train is build up out of components (List) and there are multiple component subclasses (only Wagon in this example).

In order to persist this, I thought a Table Per Type Inheritance structure would be appropriate but this is where I run into problems.

My database structure:

CREATE TABLE `Train` (
  `id` int(11) NOT NULL,
  `callsign` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_callsign_train` (`callsign`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `Component` (
  `id` int(11) NOT NULL,
  `callsign` varchar(255) NOT NULL,
  `train_FK` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_callsign_comp` (`callsign`),
  KEY `FK_comp_train` (`train_FK`),
  CONSTRAINT `FK_comp_train` FOREIGN KEY (`train_FK`) REFERENCES `Train` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `Wagon` (
  `numOfSeats` int(11) NOT NULL,
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `FK_wagon_comp` FOREIGN KEY (`id`) REFERENCES `Component` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

So now I want to create a DAO method that returns a Train object including all of it's components. I could query the Train by it's ID and lookup the corresponding components in the Component table using the train_fk, but this is where the trouble starts. The Component table only partially holds the data I need (in case of the Wagon obj the rest of the information lives in the Wagon table).

In this case it's obvious that I need to query the Wagon table for the rest of the information, but what if I have more types. How can I know what table to query for and what object to parse to?

I get that this is impossible with the current structure, but how is this done normally? What are standard solutions to handling inheritance like this in a relational database?

Thanks in advance!

PS: I'm trying to do this without the use of JPA, Hibernate or any other framework, just JDBC ;)

Because the component-subclasses can be queried individually as well, and I'd like to retrieve the Train it belongs to through the component object. I am aware of recursion though, the Train attribute will only be set it the Component is queried directly, not when queried through the Train object. — Karim Stekelenburg, Dec 11 '17 at 11:58
What you want is a disjunction i dont use them for quite a long time so i'll search a little bit more — Tiago_nes, Dec 11 '17 at 12:27
What did you learn from the trillion SO Q&As about this? Please read [ask]. — philipxy, Dec 12 '17 at 02:41
Possible duplicate of [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) — philipxy, Dec 12 '17 at 02:42
The "duplicate" you posted is solely about how the structure of the database should be. My question was focussed on the link between the database design and backend design (Java in my case). In my humble opinion, this is not a duplicate. — Karim Stekelenburg, Dec 12 '17 at 09:59

Simon Martinelli · Accepted Answer · 2017-12-11T13:08:39.037

2

I would suggest to add a column on the Component table that indicates the subtype.

When you then join train with component and all subtypes you can identify on the Java side what type you have to instantiate.

The second option could be a case statement in the select:

select *,
       case 
         when w.id is not null then 'wagon' 
       end as type 
from train t 
  join component c on c.train_FK = t.id
  left outer join Wagon w on w.id = c.id

edited Dec 11 '17 at 13:08

answered Dec 11 '17 at 12:24

Simon Martinelli

34,053
5
48
82

I thought of that but it seems a bit cumbersome doesn't it? I'm curious if there is a more efficient way to do this, or if this is the recommended pattern for this situation. – Karim Stekelenburg Dec 11 '17 at 12:38
In my opinion that's the best and most clear solution. To avoid the column you could use a case statement in the query to check which subtype the row is. But this is not obvious what it does. – Simon Martinelli Dec 11 '17 at 12:41
Well, that is quite a fair argument. Using a case statement would also force a relationship between the subtype's class name and table name, correct? That isn't desirable. – Karim Stekelenburg Dec 11 '17 at 12:50
I added an example of such a case statement in the answer. – Simon Martinelli Dec 11 '17 at 13:09
Thank you for the clarification, I think I prefer you first suggestion as well . I want to avoid a 60 line switch statement in the future :) – Karim Stekelenburg Dec 11 '17 at 13:18

How to handle inheritance in a relational database

1 Answers1