Using SecondaryTable For Flexible Database Design (With example)

Let’s consider an example. You have been working on a website selling phones since 2000. In early 2000, there wasn’t any smartphone so you may created an object Phone like this:

@Table(name = "PHONE")
@Entity
@Data
public class Phone {
    @Column(name = "PHONE_ID", nullable = false)
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String model;
    private Float price;
    private Float screenSize;
    private Float weight;
}

@Table(name = "PHONE")

@Entity

@Data

public class Phone {

    @Column(name = "PHONE_ID", nullable = false)

    @Id

    @GeneratedValue(strategy = GenerationType.IDENTITY)

    private Long id;

​

    private String model;

    private Float price;

    private Float screenSize;

    private Float weight;

}

I’m using lombok (in case you find @Data strange).

So, back in 2000s, phones were simple like that. However, with the appearance of smartphone. Things get complicated.

To accommodate to the new era, you create a new entity like this:

package com.datmt.jpa2.entity;

import javax.persistence.Entity;

@Entity
public class SmartPhone extends Phone {
    private Integer cpuCoresCount;
    private Boolean hasSDCard;
    private Boolean hasFingerprintUnlock;
    private Float rearCameraResolution;
    private Float frontCameraResolution;
}

package com.datmt.jpa2.entity;

​

import javax.persistence.Entity;

​

@Entity

public class SmartPhone extends Phone {

    private Integer cpuCoresCount;

    private Boolean hasSDCard;

    private Boolean hasFingerprintUnlock;

    private Float rearCameraResolution;

    private Float frontCameraResolution;

}

All is good.

However, problem arises when you check the database table when inserting a new smartphone.

For example, I inserted two phones, one smart, one isn’t to the table using this code:

 @Override
    public void run(String... args) throws Exception {
        Phone simplePhone = new Phone();
        simplePhone.setModel("NOKIA N8");
        simplePhone.setScreenSize(3.2f);
        simplePhone.setWeight(0.1f);
        simplePhone.setPrice(35.0f);


        SmartPhone smartPhone = new SmartPhone();
        smartPhone.setModel("iPhone 12");
        smartPhone.setPrice(999f);
        smartPhone.setScreenSize(5.7f);
        smartPhone.setWeight(0.5f);
        smartPhone.setCpuCoresCount(8);
        smartPhone.setFrontCameraResolution(20.1f);
        smartPhone.setRearCameraResolution(40.1f);
        smartPhone.setHasSDCard(false);
        smartPhone.setHasFingerprintUnlock(false);


        phoneRepository.save(simplePhone);
        smartPhoneRepository.save(smartPhone);
    }

 @Override

    public void run(String... args) throws Exception {

        Phone simplePhone = new Phone();

        simplePhone.setModel("NOKIA N8");

        simplePhone.setScreenSize(3.2f);

        simplePhone.setWeight(0.1f);

        simplePhone.setPrice(35.0f);

​

​

        SmartPhone smartPhone = new SmartPhone();

        smartPhone.setModel("iPhone 12");

        smartPhone.setPrice(999f);

        smartPhone.setScreenSize(5.7f);

        smartPhone.setWeight(0.5f);

        smartPhone.setCpuCoresCount(8);

        smartPhone.setFrontCameraResolution(20.1f);

        smartPhone.setRearCameraResolution(40.1f);

        smartPhone.setHasSDCard(false);

        smartPhone.setHasFingerprintUnlock(false);

​

​

        phoneRepository.save(simplePhone);

        smartPhoneRepository.save(smartPhone);

    }

This is what we have in the database:

As you can see, a lot of NULL columns.

Who knows what phones will have in the future. Maybe they will have some additional 100 fields. Adding more columns to database isn’t a good strategy.

SecondaryTable to the rescue

There is a very cool feature in JPA that let you put all the extra fields/attributes that are available only in subclasses to a separate table. Thus, you don’t have NULL column and still can represent the relationship between entities.

Let’s modify SmartPhone entity so it’ll use a separate table to store its new attributes.

@Entity
@Data
@SecondaryTable(name = "smart_phone", pkJoinColumns=@PrimaryKeyJoinColumn(name="PHONE_ID"))
public class SmartPhone extends Phone {

    @Column(table = "smart_phone")
    private Integer cpuCoresCount;

    @Column(table = "smart_phone")
    private Boolean hasSDCard;

    @Column(table = "smart_phone")
    private Boolean hasFingerprintUnlock;

    @Column(table = "smart_phone")
    private Float rearCameraResolution;

    @Column(table = "smart_phone")
    private Float frontCameraResolution;
}

@Entity

@Data

@SecondaryTable(name = "smart_phone", pkJoinColumns=@PrimaryKeyJoinColumn(name="PHONE_ID"))

public class SmartPhone extends Phone {

​

    @Column(table = "smart_phone")

    private Integer cpuCoresCount;

​

    @Column(table = "smart_phone")

    private Boolean hasSDCard;

​

    @Column(table = "smart_phone")

    private Boolean hasFingerprintUnlock;

​

    @Column(table = "smart_phone")

    private Float rearCameraResolution;

​

    @Column(table = "smart_phone")

    private Float frontCameraResolution;

}

Now, if I run the application and check the database, here is the result:

As you can see, no more null columns!

Let’s get back and discuss the changes.

From the new SmartPhone snippet above, you can see that I’ve added @SecondaryTable annotation specifying some fields of this class will be store on a different table (other than the table of Phone) and the join column is PHONE_ID.

Next, in the fields, if I want to move a particular field to the new table, I simply add a @Column annotation with table point to the new table name.

That’s all it takes to remove the null columns.

I don’t know about you but this make me happy.

Multiple @SecondaryTable

You may wonder if you want to link to another table, would it be @TertiaryTable and so on?

The answer is no. You still use @SecondaryTable to link to as many tables as you want.

So, for example, in the future phones will have brains. Since brains are so divine, we decide to store it to a separate table called brain_phone.

SmartPhone entity now look like this:

@SecondaryTable(name = "smart_phone", pkJoinColumns=@PrimaryKeyJoinColumn(name="PHONE_ID"))
@SecondaryTable(name = "brain_phone", pkJoinColumns=@PrimaryKeyJoinColumn(name="PHONE_ID"))
public class SmartPhone extends Phone {

//...

    @Column(table = "brain_phone")
    private Float brainCount;

}

@SecondaryTable(name = "smart_phone", pkJoinColumns=@PrimaryKeyJoinColumn(name="PHONE_ID"))

@SecondaryTable(name = "brain_phone", pkJoinColumns=@PrimaryKeyJoinColumn(name="PHONE_ID"))

public class SmartPhone extends Phone {

​

//...

​

    @Column(table = "brain_phone")

    private Float brainCount;

​

}

If we insert a new smartphone with brain count, we’ll get data populated to brain_phone table:

You can checkout the code on github to get the full project.

Đạt Trần

I build softwares that solve problems. I also love writing/documenting things I learn/want to learn.