Database Design: Internationalization of user-generated content (comments, posts...)

Question

Hi there,

Let's say I run a website giving the oportunity to users to put content online (some comments for ex...) and that I run this website under several languages / countries (locale). In addition, I don't need the comments to be accessible from a locale to another.

I'd like to know what the best database design between the following 2:

1. Create 1 table per locale:

   commentsenGB (id, txt)

   commentsfrFR (id, txt)

   etc...

2. Put all comments in the same comments table, but keep trace of the locale / language:

   comments (id, txt, id_lang)

I am pretty sure best db-design is 2) but I was considering that, in that case, the table of comments is shared between all the locales and, as a result, the number of entries will increase exponentially (nb_of_locales x quicker!) and the query time accessing the table will suffer, no? Or is:

SELECT * FROM comments WHERE id_lang = engb

The exact same query (in term of execution time) as:

SELECT * FROM commentsenGB

Thanks!

Answer 1

I would go for option two, but the critical point will be to make sure you have your table indexed correctly (as the index will be used to retrieve your data).

Not that this may not mean putting an index on the language column (unless of course you have a need to retrieve comments based on the language), but may be by the associated id (for example, your comment may be related to another object, say a forum topic).

Generally speaking I would not worry too much about database tables growing too large - databases are built too handle a large number of rows, and as you say the overhead of maintaining the additional tables could be a problem, for a variety or reasons (eg. maintenance).

Of course, another point to consider - do you need to track the "langauge" at the comment level? Consider for example that a user in the UK might enter a comment in French - all depends on the nature of your site. Other options might be to track a user location against the user?

Answer 2

Go for option 2. Simplicity. And consider how many comments do you have per day? less than 10.000 as a rough approximation, (if you have more then the website has enough cash flow to have more resources :D). Create also an index for the language_id and everything will be smooth.