Many tables v/s one table with different entities types

database-designtable

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.

Best Answer

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.

Related Question