How do I design a Localised Content table that can join to a number of different tables (the relationship needs to be generic...)

Question

I know that this isn't exactly normalised, but bringing all of the localised data throughout my application into just a few tables will help me out a lot.

I have to be able to link some generic table to a LocalisedContent table which will contain different rows for each of the localised key-value pairs of the generic table joined to it... I guess you could say that it will be a one-to-many relationship.

The problem I've found is that I am not sure how to best model this... I can think of two ways and I am not sure which one is best:

My first option is:

AnExampleOfAGenericTable
------------
AnExampleOfAGenericTableID
...other non-localised data...

AnotherGenericTable
------------
AnotherGenericTableID
...other non-localised data...

LocalisedContent
----------------
LocalisedContentID
genericTablePKName
GenericTableID
LanguageID
field
content

In the above it would be possible to get out localised content for a generic table with an SQL query like:

    SELECT AnExampleOfAGenericTableID, field, content 
    FROM AnExampleOfAGenericTable LEFT JOIN LocalisedContent 
    ON AnExampleOfAGenericTable.AnExampleOfAGenericTableID =        
    LocalisedContent.GenericTableID 
    WHERE genericTablePKName = 'AnExampleOfAGenericTableID'

Or:

    SELECT AnotherGenericTableID, field, content 
    FROM AnotherGenericTable LEFT JOIN LocalisedContent 
    ON AnotherGenericTable.AnotherGenericTableID = LocalisedContent.GenericTableID
    WHERE genericTablePKName = 'AnotherGenericTableID'

The second option seems to be, something like:

AnExampleOfAGenericTable
------------
AnExampleOfAGenericTableID
...other non-localised data...
localisedGroupID

AnotherGenericTable
------------
AnotherGenericTableID
...other non-localised data...
localisedGroupID

LocalisedContent
----------------
LocalisedContentID
localisedGroupID
LanguageID
field
content

And then I could use an SQL query like:

 SELECT AnExampleOfAGenericTableID, field, content 
 FROM AnExampleOfAGenericTable LEFT JOIN LocalisedContent 
 ON AnExampleOfAGenericTable.localisedGroupID = LocalisedContent.localisedGroupID;

Or:

 SELECT AnotherGenericTableID, field, content 
 FROM AnotherGenericTable LEFT JOIN LocalisedContent 
 ON AnotherGenericTable.localisedGroupID = LocalisedContent.localisedGroupID;

The second option seems more concise to me, but it does require me to join two FKs which seems a little strange. It also requires a lot of extra 'localisedGroupID' columns.

Ultimately both of the examples I've given may be wrong and I don't have the expertise to know the best solution to this. (Before you point out that this isn't fully normalised, I've already said I don't want hundreds of different localised data tables for each of my tables... I do want some amount of centralisation to the localisation even if it will lose me a little referential integrity.)

Ideas?

Answer 1

We use the following:

LocalizedContent:

Id   - identity
Key  - format 'TableName.ColumnName'
Value - localized value 
LanguageId - reference to the languageid
TableRowId - generic table row id

Where key in format 'TableName.ColumnName'

usage:

SELECT IFNULL(lc1.Value, name) as Name,  
       IFNULL(lc2.Value, Description) as Description
From GenericTable t 
LEFT JOIN LocalizedContent lc1
   ON (lc1.TableRowId = t.Id AND Key = 'GenericTable.Name' And LanguageID = YourLangId)
LEFT JOIN LocalizedContent lc2 
   ON (lc2.TableRowId = t.Id AND Key = 'GenericTable.Description' And LanguageID = YourLangId)

GenericTable is (Id, Name, Description)

Answer 2

Your schema remninds me of the "generalization specialization relational modeling" examples available on the web, with one important difference.

What you're calling AnExampleOfAGenericTable and AnotherGenericTable correspond to the specialized tables in the gen-spec pattern, and what you're calling LocalisedContent corresponds to the generalized table in the gen-spec pattern.

If I've understood you right every entry in the first two tables is going to have a counterpart in the LocalisedContent table, but an entry in the LocalisedContent table is going to have a counterpart in only one of the other two tables. That's exactly the same pattern as gen-spec, only backwards.

In gen-spec design, you use the same PK in all the specialized tables that you use in the generalized table. However, the PK in a specialized table is also an FK to the generalized table. And, of course, you only use the autonumber feature in the gen table.

There's nothing unnormalized about gen-spec as such.