Internationalization of a Supertype-subtype

Question

First of all, my apologies for tieing this question with another one, but that was the best way I could think to keep it simple and focused

Months ago I was introduced to the supertype-subtype as solution for a problem that was more or less easy to present but I wasn't being able to solve

I understood the basic concept and was able to make it work with my modest knowledge and it worked quite well considering the original question is more than one year old.

However today I faced a problem I couldn't solve: Internationalization

Until now I had a table named i18n with a FOREIGN KEY pointing to the PRIMARY KEY of the supertype table (Items) just like all subtypes (Car, Boat and Plane) and when querying, among all columns of all subtypes, I include the column in this internationalization table with the translated strings joining everything by the supertype key.

Something like this:

Items structure:

+----+---------------------+---------------------+
| PK | Common Attribute #1 | Common Attribute #2 |
+----+---------------------+---------------------+

Car structure:

+----+-----------+--------------+--------------+
| PK | Reference | Attribute #1 | Attribute #2 |
+----+-----------+--------------+--------------+

Boat structure:

+----+-----------+--------------+--------------+--------------+
| PK | Reference | Attribute #1 | Attribute #2 | Attribute #3 |
+----+-----------+--------------+--------------+--------------+

Plane structure:

+----+-----------+--------------+--------------+--------------+--------------+
| PK | Reference | Attribute #1 | Attribute #2 | Attribute #3 | Attribute #4 |
+----+-----------+--------------+--------------+--------------+--------------+

i18n structure:

+----+-----------+------+
| PK | Reference | Text |
+----+-----------+------+

Reference is the FOREIGN KEY

I'm not exactly sure if this is the best approach, but it works because until now only one of the columns had text passive of internationalization — let's say Attribute #1. However, today, I noticed that one of the subtypes would need two columns to be translated and the way I was doing was considering only one — let's say Attribute #4 of Planes Table.

I would like to do this right at database level instead of having some representative keyword to replace with the server-side language.

I tried to make a subquery for this second column to be translated but, not only it didn't work but that was smelling bad to me.

How could I do this?

[EDIT]

I thought for the best I should post my last attempt:

SELECT 
    `items`.`cid` AS `id`, `items`.`reference`, `items`.`GUID`,     # That's Users' Items Table

    `core`.`image`, `core`.`type`, `core`.`class` AS `classname`,   # That's the supertype

    `cars`.`attribute1`, `cars`.`attribute2`,

    `boats`.`attribute1`, `boats`.`attribute2`, `boats`.`attribute3`,

    `planes`.`attribute1`, `planes`.`attribute2`, `planes`.`attribute3`, `planes`.`attribute4`,

FROM `items`
    LEFT OUTER JOIN core ON( ( items.reference = core.iid ) )
        LEFT OUTER JOIN `catalog`.`cars` cars ON( items.reference = cars.reference )
            LEFT OUTER JOIN `catalog`.`boats` boats ON( items.reference = boats.reference OR items.reference = boats.attribute3 )
                LEFT OUTER JOIN `catalog`.`planes` planes ON( items.reference = planes.reference )
                    LEFT OUTER JOIN users ON( items.UserId = users.id )
                        LEFT OUTER JOIN i18n ON( items.reference = i18n.reference )

WHERE `i18n`.`langCode` = 'en'
        AND ( `items`.`GUID` = '4cca0b05-84b0-40af-8808-2379b32df35f' OR `items`.`GUID` = '41a0917e-8ccf-43a3-b52a-0ae0a09a2374' )

ORDER BY ( FIELD( `items`.`GUID`, '4cca0b05-84b0-40af-8808-2379b32df35f','41a0917e-8ccf-43a3-b52a-0ae0a09a2374' ) )

For this example, I used the subtype Boats to demonstrate the other column that could/should be translated.

I can retrieve all data I need of all subtypes at once with this query. It'll have several columns as NULL but when fetching data, with PHP (PDO::FETCH_NAMED, for what matters) everything works.

But I'm not being able to return the column with the text (i18n.text), only its Reference ID that matches the supertype Table (Items.reference).

I know, I know, I'm not including it as a column to be retrieved but, if I do, the first translatable column (attribute1) works, but then any other, that any subtype may have (here boats.attribute3) duplicates the value of atribute1 instead of pulling its own referenced value.

Answer 1

I personally hate having to join multiple tables to get the international variant of some text value so I make each attribute that needs to be internationalized either an XML column or a JSON column and then use the relevant functions to return the value that matches the language I need. This is an extremely extensible solution in the sense that you can add multiple languages very easily without adding new tables but does require you to modify your insert, update and selects to cope with it.

XML Example:

'<?xml version="1.0"?>
 <translations>
    <text key="en-US" value="pants"/>
    <text key="en-UK" value="trousers"/>
 </translations>'

SELECT ExtractValue([column], '/translations/text[@key="en-US"]');

JSON Example:

'{
    "en-US" : "pants",
    "en-UK" : "trousers"
 }'

SELECT JSON_EXTRACT([column], '$."en-US"');

I prefer to use JSON because it consumes less storage but I feel either approach is better than filling your database full of lookup tables. You just have to make sure that you use a character set for the XML or JSON that will support Unicode.

Answer 2

Charset?

Are you saying that every VARCHAR is latin1 today? But tomorrow you need utf8mb4?

Plan A:

1. Disable foreign keys
2. ALTER TABLE ... CHARACTER SET utf8mb4
3. Enable FKs.

Plan B:

1. Dump the schemas; manually change charset
2. dump data without schemas
3. on a fresh machine load schemas, then data

Caution: "the devil's in the details."

Phrases?

If you are saying that you have a bunch of phrases in English today, but tomorrow you need each phrase in multiple languages, then there is a lot more to do than just modify the charset.

• Change phrases to have a language-independent id.
• Have a lookup table that maps these ids into each language (phrase_id, language, phrase)
• Suggest the standard 5-char (ASCII) lanbuages, such as EN-US, EN-UK, etc. Don't bother to make separate ids for these, use them as is.
• Write a bunch of code to pick the right phrase(s).