2

I need to be able to store string messages in various languages. I guess I need a column for each language string. For example my columns could be:

ID | message_EN | message_DE | message_FR

Now, I have strings in various tables in the DB. Should I use one large string table with the above structure (or similar) where most other tables will be joined (via string ID), or have multiple tables with the same structure each related to one table? Most probably there will not be much overlap between the data of multiple tables. Are there any performance considerations with using one large table?

Thank you.

  • 1
    I would store as ID | culture | message instead. – Mansfield Nov 17 '14 at 14:08
4

This is clear example of Many-to-Many relation (M:N).
Many messages can have (be translated into) many languages.

Date structure for that has three tables:

create table Message (
  ID int primary key, 
  message text
);

create table Language (
  ID int primary key,
  language varchar(50)
);

create table Message_Language (
  message_id int, 
  language_id int,
  contraint Message_Language_Message_fk foreign key(message_id) references Message(id),
  contraint Message_Language_Language_fk foreign key(language_id) references Language(id),
  constraint Message_Language_PK (message_id, language_id)
);

It's more modular and you can add more languages easily.

What kind of queries are you performing on the tables.
Provide more info about complete data structure (and other tables).

Are you aware of data normalization in RDBMS?

  • Yes, obviously your solution makes much more sense because it is possible to add more languages easily. Still, I have no clue if one table is better or worse than many. I am aware of data normalization concepts in general - I guess using one large table for all translatable strings may be against normalization rules, but there may be cases you may break normalization in favor of performance. – pankal Nov 17 '14 at 14:16
  • If the answer is helpful please upvote it or mark it as solution/done. – Mladen Uzelac Nov 17 '14 at 14:19
  • Dah, I can't upvote, i don't have enough reputation sorry. Could you give me your opinion on using one table or many for all strings? – pankal Nov 17 '14 at 14:23
  • Personally I would go for more tables. I updated my answer with correct data structure for your problem. – Mladen Uzelac Nov 17 '14 at 14:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.