I understand this is not exactly normalised, but getting all the localized data throughout my application into only a couple of tables will assist me a great deal.
I must have the ability to link some generic table to some LocalisedContent table that will contain different rows for each one of the localized key-value pairs from the generic table became a member of into it... I suppose you can say that it'll be considered a one-to-many relationship.
The issue I have found is the fact that I don't know the proper way to model this... I'm able to think about two ways and I don't know which is better:
My first choice is:
AnExampleOfAGenericTable ------------ AnExampleOfAGenericTableID ...other non-localised data... AnotherGenericTable ------------ AnotherGenericTableID ...other non-localised data... LocalisedContent ---------------- LocalisedContentID genericTablePKName GenericTableID LanguageID field content
Within the above it might be possible to leave localized content for any generic table by having an SQL query like:
SELECT AnExampleOfAGenericTableID, field, content FROM AnExampleOfAGenericTable LEFT JOIN LocalisedContent ON AnExampleOfAGenericTable.AnExampleOfAGenericTableID = LocalisedContent.GenericTableID WHERE genericTablePKName = 'AnExampleOfAGenericTableID'
SELECT AnotherGenericTableID, field, content FROM AnotherGenericTable LEFT JOIN LocalisedContent ON AnotherGenericTable.AnotherGenericTableID = LocalisedContent.GenericTableID WHERE genericTablePKName = 'AnotherGenericTableID'
The 2nd option appears to become, something similar to:
AnExampleOfAGenericTable ------------ AnExampleOfAGenericTableID ...other non-localised data... localisedGroupID AnotherGenericTable ------------ AnotherGenericTableID ...other non-localised data... localisedGroupID LocalisedContent ---------------- LocalisedContentID localisedGroupID LanguageID field content
After which I possibly could make use of an SQL query like:
SELECT AnExampleOfAGenericTableID, field, content FROM AnExampleOfAGenericTable LEFT JOIN LocalisedContent ON AnExampleOfAGenericTable.localisedGroupID = LocalisedContent.localisedGroupID;
SELECT AnotherGenericTableID, field, content FROM AnotherGenericTable LEFT JOIN LocalisedContent ON AnotherGenericTable.localisedGroupID = LocalisedContent.localisedGroupID;
The 2nd option appears more concise in my experience, however it does require me to become listed on two FKs which appears strange. Additionally, it requires procuring 'localisedGroupID' posts.
Ultimately each of the good examples I have given might be wrong and that i not have the expertise to understand the very best means to fix this. (Before you decide to explain this is not fully normalised, I have already stated I'm not going 100s of various localized data tables for every of my tables... I actually do want some quantity of centralisation towards the localisation even when it'll lose us a little referential integrity.)
Your schema remninds me from the "generalization specialty area relational modeling" good examples available on the internet, with one important difference.
What you are calling AnExampleOfAGenericTable and AnotherGenericTable match the specialized tables within the gen-spec pattern, and what you are calling LocalisedContent matches the generalized table within the gen-spec pattern.
If I have understood you right every entry within the first couple of tables will have a counterpart within the LocalisedContent table, but an entry within the LocalisedContent table will have a counterpart in just among the other two tables. That's the identical pattern as gen-spec, only backwards.
In gen-spec design, you apply the same PK in most the specialized tables that you employ within the generalized table. However, the PK inside a specialized table can also be an FK towards the generalized table. And, obviously, you simply make use of the autonumber feature within the gen table.
There is nothing unnormalized about gen-spec as a result.