Design for bi-lingual website

Question

I am building a bi-lingual website and am wondering about the best design to use for the product database. The site will only ever use two languages, there are absolutely no plans to modify the site to use more than two languages in the future.

So with that in mind, what would be the easiest approach to designing the schema for the product database to support two languages? I can think of two ways of doing this:

1. Have a separate record for each translation. Something like this:

product table:
id
date_created
price

product_info table:
id
product_name
description
language
product_id

With this design, we would create two entries in the product_info table for every entry in the product table. The two entries in the product info table would consist of one record containing the English text and the other record containing the Japanese text, and the language field acts as a flag to indicate the language that the record is stored in.

1. The second approach would be to simply keep one record for both translations, but each record will have a Japanese and an English field to store the corresponding translation. Something like this:

product table:
id
date_created
price

product_info table:
id
product_name_en
description_en
product_name_jp
description_jp
product_id

I think the first design would make sense if we are working with more than 2 languages in the site, or if we have plans to extend the site into 3+ languages in the future. But in our case, the site will never use more than 2 languages. So, I am not sure if this is the best approach for design if we will only ever have 2 languages on the site. The second design seems like a simpler approach for 2 languages, but I am afraid I may be overlooking something here and am having a hard time deciding which approach to use.

Any thoughts/opinions on these approaches? I would love to hear.

Thanks!

Answer 1

Never say never. I'd choose a separate product info record that contains one set of info and a language (the first option). It's a piece of cake to do the necessary checks for multilingual code (just add a language id in a handful of queries if you build it right).

You should make a general handler that determines the languages you want. The products and each other element that will generate output, should have a set of properties that use that language internalle. E.g. if you got a Product object, it will give you a getDate and GetPrice method, but also a getProductName and getDescription method. Internally it will use the globally available language to get the right information and return them via those methods.

That way, you will only need one place to determine the right language based on the (sub) domain or a given session variable, and you will only need a handful of places, where you load the data from the database, to take that language in account. For the rest of your website, the only exception being the CMS, there won't even be such a thing as a language. Everything will be completely transparent. It will just display 'ProductName', not knowing it could even have different translations.

It will hardly cost you any extra development to do it this way, and your site will support as many languages as will ever be needed in the future.

Answer 2

As you are in favour of developing the schema at a greenfield project, you should always choose the option that's fitting your usecase best. Your talking about two languages and NOT MORE, but exactly that were most database-designers thinking of their projects, before they got instructed of their customers to add some more functionality or languages in your case.

If there's an option that doesn't limit your possibility to add a few more languages with ease, that at least DOESN'T extend the complexity and time to implement it, then why would you choose the other one?

So if i were you, my decision was the first option not because you can extend it, but because of it's a clean design you can easily where you can abstract the multi-language thing in other application layers easily.

You can identify the language of the records with a single column and don't have to address multiple colums (by name) for each attribute you need for a specific language. For performance issues you can use multi-column indices matching your searching criteria (id+language_id).