How to do JoinTable annotation mapping

Question

Hi Gurus,

I have a design/programming question. First I wanna ask if my design is good and then I wanna know how to.

What I wanna do is to have an i18n page, which may or may not have a translation. e.g. Page A has English & Japanese, and Page B may only have English

This is the DB layout

page
----
id int
topic varchar(128)
content varchar(1024)

page_l10n
---------
id int fk
topic varchar(128)
content varchar(1024)
locale_id int fk

locale
------
id int
name varchar(8)

To select, I do something like this

SELECT
    COALESCE (hl.topic,h.topic) , COALESCE(hl.content, h.content)
    FROM page h 
    LEFT JOIN (page_l10n hl, `locale` l) ON (h.id=hl.id AND l.id=hl.locale_id) AND l.locale = 'ja_JP'

So my question is, if this is an ok db layout (or do you guys suggest to use 2 table only and have the locale as a column in the page table? or other way of doing it?), how do I do my model (POJO)?

I am using JPA for R/O mapping and hibernate for the DB connection.

Please Advise Thanks in advance~

Answer 1

I'm going to assume that your content is dynamic (e.g. provided / changed by end users). If it's not, you might want to take a look at resource bundles instead.

It's hard to critique your database layout without understanding what you're trying to achieve and you've provided very little description. But, assuming topic and content are the only attributes your pages will have and both of them need to be localized I would suggest the following:

1) id column of the page table looks to be a surrogate key (since it's of int type), which is fine if it's referenced from some other table. However, if you're going to be retrieving page content directly you need some way to address the pages without having to hardcode their ids (what I mean is if I need to get content for some/path/pageA.html, how would I know that I should look for page with ID = 173?) Therefore, if that's the case you may want to store a natural key in that table as well.

2) I'm not sure you need topic and content columns in the page table; why not store them in page_l10n for appropriate locale? There are cases where keeping them in page table may be appropriate, but I don't think yours is one of them.

3) Having a separate locale table may make sense if you only want to support translations into a limited subset of locales as opposed to all available locales.

As far as mapping goes, JoinTable annotation is mostly used for many-to-many mapping or (somewhat uncommon) unidirectional one-to-many mapping; both of them are not applicable in your case. You'll need a simple collection managed from child's end. Something along the lines of:

// in your Page class
@OneToMany(mappedBy="page")
public List<PageLocalization> getPageLocalizations() {

// in your PageLocalization class
@ManyToOne
public Page getPage() {