Forum Moderators: open

Message Too Old, No Replies

db design for multilingual website

         

PHPycho

5:38 am on Sep 21, 2009 (gmt 0)

10+ Year Member



For db design for multilingual website, i have come up with following solutions
and don't know which one is perfect also want to know if there is any other good options

Obviously firstly we need language table as:
----------------
¦languages
----------------
¦id
¦code
¦flag
¦is_active
----------------
Possible solutions
1> using one table(suppose we have 'news' table for example)
------------
¦news
-------------
¦id
¦language_id
¦title
¦body
¦created_by
¦created_date
¦is_active
:
:

2> Using two tables
------------
¦news
-------------
¦id
¦created_by
¦created_date
¦is_active
:
:

---------------
¦news_contents
---------------
¦id
¦news_id
¦language_id
¦title
¦body
----------------

3> Using one master table for all tables which needs translation
--------------
¦news
--------------
¦id
¦title
¦body
¦created_by
¦created_date
¦is_active
---------------
------------
¦categories
-------------
¦id
¦title
¦description
¦is_active
--------------

master table:
---------------
¦translation
---------------
¦id
¦language_id
¦table_name
¦table_field
¦translated_value
----------------

I have found all these structure so far, dont know which one is better as i havent used such in my multilingual website.
I would like to know the pros n cons for these structures, best db structure.

Note: Any no of languages can be added ranging from 1 -> N

Thanks for the help again in advance.

physics

11:45 pm on Sep 25, 2009 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



I prefer #2.