Many tables v/s one table with different entities types

Question

My requirement is as follows: App with internationalization and mobile v/s desktop app text support. So, I have came up with below columns in the table which I will certainly need:

    "CODE" VARCHAR2(30 BYTE) NOT NULL ENABLE,  //Unique identifier
    "TEXT_EN" VARCHAR2(1000 BYTE),  //EN text for desktop version of app
    "TEXT_FR" VARCHAR2(1000 BYTE),  //FR text for desktop version of app 
    "TEXT_EN_SHORT" VARCHAR2(500 BYTE), //EN text for mobile version of app
    "TEXT_FR_SHORT" VARCHAR2(500 BYTE),   //FR text for mobile version of app

Now, as per the device type and language I will fetch data from appropriate column, I am clear in this part.

Confusion: I will have several drop downs in the app like country, province, medicine names etc. So, should

1. I have one more field in the same table "FIELD_TYPE" which will identify whether this particular row is country or province etc. So, to fetch all country details my query will have FIELD_TYPE = "Countries"
2. Or I should have different table for each - country, province, medicine names etc.

Which is more space efficient and query performance efficient and WHY?

Here is the related question posted by myself and kept is separate to isolate the concern, but you may choose to post your overall answer in either question.

I know the probable choice in each case, but I want to weigh and understand the pros and cons in each case, and why I should choose one and not other!

It is discussed here but for same w.r.t entities.

Answer 1

You could really use either option: one translation table with a FIELD_TYPE partitioning attribute, or a one translation table for each table requiring localization.

The pros and cons of either approach are not likely to be related to the criteria you ask about: space or query performance. These aren't necessarily the most important criteria for you to be concerned about either. Unless you have really substantial scalability requirements then you should focus on code maintainability as a differentiator rather than disk space or query performance.

Another factor to consider is how much time you want to spend explaining your choices to other developers. If you use a single table with a partitioning attribute, then you are opting for something very much like Entity Attribute Value (EAV) which is widely frowned upon for many mostly good reasons.

On the other hand, having a translation table per translatable object lets you tailor your column sizes to the actual data rather than picking the largest common denominator and also use tools like declarative referential integrity which are generally seen as best practices.

Answer 2

Well, I won't use a single table for this problem. If you need another language (just as example german), you'll need to extend your table by two columns.

I would suggest a solution like this.

Table Translations

Code (PK) (Your translation code)
type (PK) (mobile, desktop, maybe something else later)
language_id (PK) (your language code - 1:EN, 2:FR, 3:DE,...)
value (your translation)

This way you can easily extend your languages. You just need to add the code column to every table you want to be translatable.

Here is an example:

Code    Type    Language_Id     Value
28      0       1               Login
28      0       2               Entrer
28      0       3               Anmelden
28      1       3               Verbinden
29      ...     ...             ...

This is the example shows how the translation for Item (code) 28 will work. You'll see the translation for each language (1:En, 2:Fr, 3: DE) and the different types (0:desktop, 1:mobile).

Using this approach, you'll be able to use one translation string for multiple codes. Just in case you have multiple different strings to be translated in german (which may occur) but the different words in german, are just one word in english. Using this way you can map all english words to the same code and save space. But I won't compare the approaches just in space. Both approaches just won't be too big. Even on many hunderthousands of translations, we're talking about 100MB (maybe)? I would use it, just as it's quite simple to implement and to maintain, for everyone.