2

My boss is planning on a new db and wants to support multilingual data in this manner:

LocalizedDescs (Guid / LanguageGuid being the primary key)

  • Cluster
  • Guid
  • LanguageGuid
  • Desc

ProductCategories

  • Cluster
  • Guid
  • (...)

Products

  • Cluster
  • Guid
  • CategoryGuid
  • DetailedDescGuid
  • (...)

The way it works is that every table is having a Guid field, used as the primary key. The LocalizedDesc table's Guid field, in turn, corresponds to any guid used in tables throughout the db, making it a parent table to every table in the system.

In rare cases where a table record needs another localized resource, an additional field is used in the table that will also point to a LocalizedDesc record. As an example, the Products table has a DetailedDescGuid that is meant to contain a throughout, longer description of a product. This way we have both a summary description and a detailed description, both of which can be localized to different languages.

Originally, we were supposed to have a LocalizedDescGuid field in each table needing a description. But my boss claims the db indexes will be smaller if we do this the other way.

Design-wise, what is this solution's worth? Was it better when it used an additional field in each table? Or are we doing this all wrong?

  • is your requirement that every product have multiple localized description or just some? IE are you providing an application that users will see everything in English or everything in French...and what does the size of indexes have to do with anything? Storage is cheap, the difference in access times relative to index size is not significant unless you are up to terabyte size.. – kevinsky Jun 19 '15 at 17:33
  • @kevinsky It depends. Users that are only authorized to look at the data will see a description that corresponds to their current regional settings, while users that perform data maintenance will obviously be able to see everything. As for the indexes, what I meant is that back when we had LocalizedDescGuid in every tables, we were creating an index on each of them. But now that we're using any record's Guid (be it a product, a category, whatever) as the matching Guid inside LocalizedDescs table, that's one less index per table. – Crono Jun 19 '15 at 17:39
2

I would suggest the following structure due to my experiences in some other applications.

First of all I would build a language table:

language_id (PK)
iso_country_code
iso_language_code
codepage
translation_id (FK)

The language_id would represent the primary key. Each available language is added to this table. iso_country_code can contain the ISO country code (GB - Britain, DE - Germany, ...). iso_language_code can be used to cover different located languages (e.g. en_US, en_GB,...) codepage is the codepage which will be sent out. translation_id more on this later on.

The second thing should be a translation table. The translation table should hold back all translations for every translatable term.

translation_id PK
language_id PK (FK -> language)
term

The table consists of an combined primary key over translation_id and language_id which will prevent double insertion. language_id will refer to the language table. term itself will just be the translated term (e.g.: Table in german -> Tabelle)

The next thing will happen on each table which holds translatable items. For example a Table which holds your products for example called products.

product_id PK
product_information
product_price
...
translation_id (FK)

Only the translation_id is needed. It will refer to the translation table and retrieve the correct translation. The application can give or user session can be joined into the statement to filter the proper language for the logged in user.

The other positive thing on this solution is that if you have multiple translations in different tables which all means the same but in a different context and all can have the same translation_id, they already can share the same translation_id which will reduce your data weight and will improve the translation.

Hopefully this will give you a good hint and help you to improve your solution.

  • This is pretty much my idea on how it should be done as well. The problem here is that I can't seem to convince my boss he needs a Translation_Id field, when in fact all of our data records in every possible table have a unique guid as their pk, making it pointless. – Crono Jun 19 '15 at 18:36
  • By the way. Beware of GUIDs as PK. Don't set a clustered index on it. This will decrease performance. – Ionic Jun 19 '15 at 18:41
  • Oh no my boss does use numeric fields for clustered indexes, they're just not set as the primary key. We just never refer to them. Again, not sure if this approach is any good but I'm not so much of a db guy. – Crono Jun 19 '15 at 18:52
  • Well if you won't use them, you just have them to waste space and give a proper order to your tables. Do you have a no clustered index on the GUIDs? Hopefully. :-) what is the background to use the GUIDs instead of normal id's in your application? By the way a info for your chef: I've just needed to refactor a database which used GUIDs instead of a specific translation_id. The translation table had hold some equal terms more than 50.000 times just because they have different GUIDs instead one equal translation_id. :-D – Ionic Jun 19 '15 at 19:07
  • Again I'm not a db guru but from my understanding the sole purpose of the clustered indexes in our tables is indeed to provide an efficent ordering for table records. I'll edit the post to show the fields. As for equal terms, the belief is that it's highly unlikely we'll ever have duplicated descriptions, and if we do that will just be coincidential and we'll still want to allow a divided maintenance, as we wouldn't want to fall in a situation where changing one description for a product would mean changing it for multiple other db objects. – Crono Jun 19 '15 at 19:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.