Table with multiple languages

Question

Hi,

I want to store multiple translations of an text in a MSSQL database.

for example one table product with the columns

• ProductId
• ProductPrice
• ProductNameId
• primairy key = ProductId

and a table with the productnames

• Id
• Language
• Name
• primairy key = Id and Language

How can i create an foreign key to link the Product.ProductNameId to ProductName.Id

Answer 1

I think that the most appropriate pk-fk relationship is between ProductId on the Product table, and a ProductId (without Language) in the ProductName table. The field on the Product table is the pk, and the field in the ProductName table is the fk. That will ensure that there are no records in the PeoductName table that don't match with a record in the ProductName table.

If you want to treat Language similarly, then you can create a Language table with a LanguageId field. then, make a LanguageId field in the ProductNames table, and make that a fk.

when you retrieve product information, you JOIN Product and ProductName on their ProductId fields, and then specify the LanguageId in the WHERE clause.

Answer 2

I'd change the ProductNames table to:

Id
ProductId
Language
Name

With these constraints:

• Primary key on Id
• Foreign key on ProductId
• Unique constraint on (ProductId,Language)

Answer 3

Another SO question with a good answer: Schema for a multilanguage database