1

I'm working on multi-lang website. Trying to design optimal db schema for this purpose.

enter image description here

As you see, there are 2 tables: langs and menu. My idea is following:

For ex. lets take a look at multi-language navigation generation from MySQL table. In PHP backend, while generating navigation from database

  • Get all data from menu table's row
  • Left join second table - langs (by name field of menu table ) and get data from defined language's column (for ex. en, ru)

How do you think, is it optimal way, or is there more efficient solution? Please, give me database related answers, not file. (for ex. gettext,... etc)

Improve this question

2 Answers 2

Reset to default
1

It would be better if the langs table contained a language column, with one row for each translation. This allows you to add another language later and only have to apply data changes (rather than having to re-write queries).

As you've already implied, performing a left join and falling back to a default language held directly in the menus table is also a good idea (in which case, you don't need to hold a translation for e.g. en in the langs table, since the english version will always be available).

You may also want to consider storing country-specific translations (if, say, there are multiple spanish speaking countries where the translations can be different), and following a fallback strategy of finding: language-country, language, English translations.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

@TuralTeyyuboglu - yes, although I'd make the primary key of the langs table ID, language so that all of the translations for menu 1 have an ID of 1.
Should I use foreign key, like the second solution? Will iw work faster?
@TuralTeyyuboglu - foreign keys aren't, primarily, a mechanism for improving access speed. Their primary purpose is to ensure referential integrity -
1

You can normalize further like so:

  • tokenname: id, token (eg, (1, program), (2, menu))
  • localizations: id, tokenname_id, lang, text

Tables that reference strings then refer to the string by tokenname.id as a foreign key.

Note that you will need some kind of mini templating language for text localizations.

Improve this answer

1 Comment

"Tables that reference strings then refer to the string by tokenname.id as a foreign key." I don't understand well this part. Please, either explain here or I'm waiting you in chatroom. chat.stackoverflow.com/rooms/6113/multilang-website

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.