Do you think is necessary to create an auxiliary translate table for table that has only two elements (id and name) 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),
)
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.