Do you think is necessary to create an auxiliary translate table for table that has only two elements (id and name)

Question

I have a question.

In my database I have an Allergen table that has two elements: Id and Name.

I want that my Allergen table has translations, so what is better ? Create an auxiliary translate table that name could be AllergenTranslate or create languangeId directly in the table Allergen and change its name for AllergenTranslate.

Example:

CREATE TABLE [Allergen] (
  [Id] int PRIMARY KEY IDENTITY(1, 1)
)

CREATE TABLE [AllergenTranslation] (
  [Id] int PRIMARY KEY IDENTITY(1, 1)
  [AllergenId] int,
  [LanguageId] int,
  [Name] nvarchar(255),
)

OR DIRECTLY AllergenTranslation table

CREATE TABLE [AllergenTranslation] (
  [Id] int PRIMARY KEY IDENTITY(1, 1)
  [LanguageId] int,
  [Name] nvarchar(255),
)

OR DIRECTLY Allergen Table without Translation table name

CREATE TABLE [Allergen] (
  [Id] int PRIMARY KEY IDENTITY(1, 1)
  [LanguageId] int,
  [Name] nvarchar(255),
)

Answer 1

Definitely the first one with both Allergen and AllergenTranslation tables.

The reason is that Allergen is likely to be referenced in other places. Say you have a table of medicines each of which is effective against a subset of allergens. What would be the foreign key in this Efficacy table? You don't want that FK to have LanguageId in it at all - the medicine's response is not conditional on the patient's speech. Nor do you want to repeat rows in Efficacy for each language as this is redundancy leading to duplication and errors. Think of the changes required to introduce another language.

Answer 2

For the most part this is going to depend on how often this will be used and how often multiple languages will be needed for the same allergen. Unless this is going to be the cornerstone of your application (IE a transaction table or some kind of lookup table which 90%+ of your transactions will use) I would not overthink it. I have heard it said before:

...premature optimization is the root of all evil...

Wikipedia - Premature Optimization

With that said, in my personal opinion I would separate your names and your actual allergens. Since they are two different data elements. Likely you will want to be able to build code and query the data by allergen, not by it's name.

EX: You will want to write code/reports around people who are allergic to latex (in English), and reference that with a common name/ID/Code. You most likely will not want to reference it on the backend by all of it's various names (which will grow as the number of languages/countries this application uses grows).

If it was me implementing the solution, I would have two separate tables Allergen and AllergenTranslation. I would do something like what you have above. I would recommend that you have some kind of common name or code that is either always the same language or is language ambiguous. That way as you move these records from environment to environment, you don't have to ensure that the numeric id Allergen.Id is the same across the environments and you don't have to keep a list of what each ID means. Something like:

CREATE TABLE [Allergen] (
  [Id] int PRIMARY KEY IDENTITY(1, 1),
  [CommonName\Code] nvarchar(255) NOT NULL UNIQUE
)

CREATE TABLE [AllergenTranslation] (
  [Id] int PRIMARY KEY IDENTITY(1, 1)
  [AllergenId] int,
  [LanguageId] int,
  [Name] nvarchar(255),
)

You could always fulfill the need of that CommonName\Code column if you always have at least one record in [AllergenTranslation]. I personally would not want to have that be a requirement (but I also work in an environment that basally exclusively works in English).

Let me know if there is anymore help that can be provided or if any part of this does not make sense.

Answer 3

Thank you for your answers. Finally I chose the first option with some changes.

I will put in my Allergen Table some kind of Code or CommonName that will be common in my AllergenTable for the same element but has different elements in my AllergenTable translate. I think is good idea.

So thanks for all.

And thanks for your help!