Child entity with Secondary Table and Compound Key spread over two tables

Question

After much debate on choosing an approach for an internationalized database design I went with having two tables for each table that requires translation. I'm having some trouble with ORM in the following case.

So I have the following tables:

cat          cat_t               subcat            subcat_t 
------       -------             ----------        ------------
id (pk)      cat_id(pk,fk)       id(pk)             subcat_id(pk,fk)
             locale(pk)          cat_id(fk)         locale(pk)
             name                                   name

@Entity 
@Table(name = "cat")
@SecondaryTable(name = "cat_t",
    pkJoinColumns = @PrimaryKeyJoinColumn(name = "cat_id", 
                                          referencedColumnName = "id"))
@IdClass(TranslationKey.class)
public class Category {
    @Id 
    private long id;

    @Id
    @Column(table = "cat_t")
    private String locale;

    @Column(table = "cat_t")
    private String name;

    @OneToMany(fetch = FetchType.LAZY)
    private List<SubCategory> subCategories;

    // getters and setters
 }

@Entity
@Table(name = "subcat")
@SecondaryTable(name = "subcat_t",
    pkJoinColumns = @PrimaryKeyJoinColumn(name = "subcat_id", 
                                          referencedColumnName = "id"))
@IdClass(TranslationKey.class)
public class SubCategory{

    @Id
    private long id;

    @Id
    @Column(table = "subcat_t")
    private String locale;

    @Column(table = "subcat_t")
    private String name;

    @Column(name = "cat_id")
    private long categoryId;

    // getters and setters
}

public class TranslationKey implements Serializable {

    private long id;
    private String locale;

    // getters and setters
}

My goal is for subcategories to only pull back the subcategories for the locale of the parent. I think I have some options including, querying the subcategories separately and making the field transient or pull everything back (all subategories for all languages) and then just filter out the ones I want.

The issue I have had with @JoinColumn is that locale is part of the secondary table for both cat can subcat and so when I try the referencedColumn that may not be allowed since its not in the same table?. I'm using EclipseLink but I'm not really tied to a JPA Provider.

Any help/guidance is much appreciated

Answer 1

cat seems to have a one to many relationship with cat_t, since there can be many rows in cat_t for a single cat entry. This is not ideal to have in a single Entity, since it means you will have instances sharing data, and endless headaches if you make changes.

A better approach that makes updates possible is to map the cat and cat_t to separate Entities, where Cat has a collection of Cat_t?Local instances. The entity mapping to cat_t can use the local and its ManyToOne back pointer to Cat as its Id:

@Entity
@IdClass(TranslationKey.class)
public class Local {
  @ID
  private String locale;
  @ID
  @ManyToOne
  private Cat cat;
}

public class TranslationKey{
  string local;
  long cat;
}