CODEWITHSUNDEEP
databasedatabase-designschemamultilingual
JonnyK
JonnyK

Reputation: 7

Multilanguage table design problem

I am having an implementation issue with this basic multilang design.

Table content 

content_id bigint(20) NOT NULL,  
lang_id int(5) NOT NULL,   
word varchar (500) NOT NULL,  
created datatime,

Now the issue is with implementing this. I assume in the application i am declaring each word on the page with the content_id like 1,2,3,4,5... where 1 = "Hello" 2 = "Welcome to xxxxx" etc. Right? So the problem is I give "Hello" = 1 in the code. So in the table it is like this 1, 23 (for english), Hello.

But now if i have a Spanish session, how will i find "Hello" in the table? Because I hardcoded 1 in the code to be "Hello" so the system will always lookup this up and find the english text. The only work around i see is to have separate tables per language?

Upvotes: 0

Views: 476

Answers (1)

Chris B. Behrens
Chris B. Behrens

Reputation: 6295

You need to make lang_id and content_id a composite primary key. You'll end up with data that looks like this:

1         Hello            1
1         Hola             2
2         Thanks           1
2         Gracias          2
2         Danke            3

You might even want to break out the English translation into a separate table to make it easier for you as the developer (since you speak English)...then you know that WordId 1 is, canonically, "Hello". Foreign key to language table, obviously. If you do this, you might want a surrogate key for the primary key as well...that simplifies updates.

Upvotes: 1

Related Questions