Mysql – unusual relation, binary aspect

We've done a lot of this, and (administrative) users were allowed to fix the translations live. (You still might want a caching layer, but I'm totally down with driving this with a real database and not resource files - it gives you a ton of power to query and find things which need to be translated, etc). I think your schema is probably fine, so I'll just pass on some stuff we learned in the hope that it's useful.

One thing you have left out is phrases with insertion points. In the example below, the order is reversed and the language is still English, but this could very easily be two different languages - pretend this is just two languages who normally put things in a different order.

Hello, <username> you have <x> points!

You've got <x> points to spend, <username>!

In our pre-.NET, we had a routine which did the insertion so the phrases would look like this:

Hello, {0:username} you have {1:points} points!

You've got {1:points} points to spend, {0:username}!

This would obviously simply be used in your code as <%= String.Format(phrase, username, points); %> or similar

Which helped the translator a bit. But .NET String.FOrmat doesn't support commenting within the format string, unfortunately.

As you say, you would not want to handle that in your php with locale awareness or meta phrases.

So what we had was a master phrase table:

phraseid, english, supplemental info

and a localized table:

phraseid, localeid, translation

You've also assumed with INNER JOINS that the localized versions exist - we tended to leave them out until they were translated, so that query of yours would end up returning nothing at first (not even the default)

If a translation didn't exist, ours defaulted to English, then fellback to code-provided (in case the database didn't have the ID, and it was also clear from the code what phrase identifier "TXT_LNG_WRNNG_INV_LOW" was actually trying to get) - so the equivalent of this query is what we used:

SELECT COALESCE(localized.translation, phrase.english, @fallback)
FROM DUAL
LEFT JOIN phrase
    ON phrase.phraseid = @phraseid
LEFT JOIN localized
    ON localized.phraseid = phrase.phraseid
    AND localized.localeid = @localeid

Obviously, you might get all the things at one time using your page system.

We tended not to link things to the page because they were re-used a lot between pages (and not just in page-fragments or controls), but that's certainly fine.

In the case of our Windows-native apps, we used reflection and a mapping file from control to translation tag so that translation did not require re-compiles (in pre-.NET apps we had to tag the controls using the Tag or other special properties). This is probably a little more problematic in PHP or ASP.NET MVC, but possible in ASP.NET where there is a full-blown server-side page model.

For testing, you can obviously query to find missing translations very easily. To find places which need to be tagged, translate the entire phrase dictionary using pig-latin or Klingon or something like replace every non-space character with ? - the English should stand out and let you know that some naked plaintext has crept into your HTML.

The answer depends quite a bit on what you want to do. One option is EAV as one of the notes pointed out. Another option is what I call EAV-lite which is to have stored procedures for adding columns to an additional "extended attributes" table. This gives you something of a fixed schema with extended attributes. If you need to add something else you can do it. Of course you need EAV-like catalogs to tell you which attributes to search on, which is why I call it EAV-lite.

What we did in LedgerSMB was to create two additional tables for this catalog info: custom_table_catalog and custom_field_catalog for tracking these extensions. Then the extends_* tables hold these extended attributes horizontally.

If you were on Postgres you could use hstore for these extended attributes too. In 9.1 you could even use JSON. Of course care needs to be taken in breaking the standard normalized approach here. I would recommend EAV-lite more likely.

Edit: adding sample code. Note that this works on PostgreSQL but on MySQL you will need to rewrite the functions as stored procs and possibly some other things.

CREATE TABLE custom_table_catalog (
table_id SERIAL PRIMARY KEY,
extends TEXT,
table_name TEXT
);

CREATE TABLE custom_field_catalog (
field_id SERIAL PRIMARY KEY,
table_id INT REFERENCES custom_table_catalog,
field_name TEXT
);

CREATE OR REPLACE FUNCTION add_custom_field (table_name VARCHAR, new_field_name VARCHAR, field_datatype VARCHAR) 
RETURNS BOOL AS
'
BEGIN
        perform TABLE_ID FROM custom_table_catalog
                WHERE extends = table_name;
        IF NOT FOUND THEN
                BEGIN
                        INSERT INTO custom_table_catalog (extends)
                                VALUES (table_name);
                        EXECUTE ''CREATE TABLE '' ||
                               quote_ident(''custom_'' ||table_name) ||
                                '' (row_id INT PRIMARY KEY)'';
                EXCEPTION WHEN duplicate_table THEN
                        -- do nothing
                END;
        END IF;
        INSERT INTO custom_field_catalog (field_name, table_id)
        values (new_field_name, (SELECT table_id
                                        FROM custom_table_catalog
                WHERE extends = table_name));
        EXECUTE ''ALTER TABLE ''|| quote_ident(''custom_''||table_name) ||
                '' ADD COLUMN '' || quote_ident(new_field_name) || '' '' ||
                  quote_ident(field_datatype);
        RETURN TRUE;
END;
' LANGUAGE PLPGSQL;
-- end function

CREATE OR REPLACE FUNCTION drop_custom_field (VARCHAR, VARCHAR) 
RETURNS BOOL AS
'
DECLARE
table_name ALIAS FOR $1;
custom_field_name ALIAS FOR $2;
BEGIN
        DELETE FROM custom_field_catalog
        WHERE field_name = custom_field_name AND
                table_id = (SELECT table_id FROM custom_table_catalog
                        WHERE extends = table_name);
        EXECUTE ''ALTER TABLE '' || quote_ident(''custom_'' || table_name) ||
                '' DROP COLUMN '' || quote_ident(custom_field_name);
        RETURN TRUE;
END;
' LANGUAGE PLPGSQL;

I don't know if MySQL allows for alter table statements to be done dynamically, so worst case scenario you might have to maintain these functions in the app layer.

The application then selects the contents of custom_table_catalog and custom_field_catalog and caches these in memory. When an item is saved or retrieved, these are then used to create SQL CRUD functions. Currently they are used for orders, invoices, goods, and services.