Hi,
I have a number of database tables that contain name and description columns which need to be localized. My initial attempt at designing a DB schema that would support this was something like:
product
-------
id
name
description
local_product
-------
id
product_id
local_name
local_description
locale_id
locale
------
id
locale
However, this solution requires a new local_ table for every table that contains name and description columns that require localization. In an attempt to avoid this overhead I redesigned the schema so that only a single localization table is needed
product
-------
id
localization_id
localization
-------
id
local_name
local_description
locale_id
locale
------
id
locale
Here's an example of the data which would be stored in this schema when there are 2 tables (product and country) requiring localization:
country
id, localization_id
-----------------------
1, 5
product
id, localization_id
-----------------------
1, 2
localization
id, local_name, local_description, locale_id
------------------------------------------------------
2, apple, a delicious fruit, 2
2, pomme, un fruit délicieux, 3
2, apfel, ein köstliches Obst, 4
5, ireland, a small country, 2
5, irlande, un petite pay, 3
locale
id, locale
--------------
2, en
3, fr
4, de
Notice that the compound primary key of the localization table is (id, locale_id), but the foreign key in the product table only refers to the first element of this compound PK. This seems like 'a bad thing' from the POV of normalization.
Is there any way I can fix this problem, or alternatively, is there a completely different schema that supports localization without creating a separate table for each localizable table?
Update:
A number of respondents have proposed a solution that requires creating a separate table for each localizable table. However, this is precisely what I'm trying to avoid. The schema I've proposed above almost solves the problem to my satisfaction, but I'm unhappy about the fact that the localization_id foreign keys only refer to part of the corresponding primary key in the localization table.
Thanks,
Don
