CODEWITHSUNDEEP
javamysqlinheritancerelational-databasedao
Karim Stekelenburg
Karim Stekelenburg

Reputation: 643

How to handle inheritance in a relational database

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 ;)

Upvotes: 1

Views: 501

Answers (1)

Simon Martinelli
Simon Martinelli

Reputation: 36163

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

Upvotes: 2

Related Questions