I get some issues when i implement product_description table with language .
my process is that i have default table product_description_en to store description and when a client installs new language (Chinese) the php script will create new table product_des_ch and then put the all default data(from the English table) in to the newly created table.then the client can update . My problems are
2.If we use same table for all languages(the records will be around 500,000) then are there any per performance issues 3.what is the best way for large amount of records to store , i mean same table or separate tables.
Thanx Az
Updated: This is sample product_description table structure for English table and Japan .What you think about this table(we store the all records in a same table and when the client inserts new record for different language only inserting new records ) ,Any feedback please ?
+---------------------------------------------------------------------------+
| product_id | name | desc | meta_name | meta_desc | key_words | lan_code |
+---------------------------------------------------------------------------+
| 1 | A | D| m1 | m_d1 | k1 | en |
+---------------------------------------------------------------------------+
| 1 | A | D| m2 | m_d2 | k2 | jp |
+---------------------------------------------------------------------------+
Basic RDBMS design wisdom would put a huge red flag on anything that dynamically alters the table structure. Relational databases are more than flexible enough to handle pretty much any situation without requiring such measures.
My suggestion as for the structure would be to create a single Languages table to store the available languages, and then a Phrases table to store all the available phrases. Then use a Translations table to provide the actual translations of those phrases into the available languages. Something that might look like this:
Language
+----+---------+
| id | name |
+----+---------+
| 1 | English |
| 2 | Chinese |
+----+---------+
Phrase
+----+-------------+
| id | label |
+----+-------------+
| 1 | header |
| 2 | description |
+----+-------------+
Translations
+-------------+-----------+-----------------+
| language_id | phrase_id | translation |
+-------------+-----------+-----------------+
| 1 | 1 | Header |
| 1 | 2 | Description |
| 2 | 1 | 头 |
| 2 | 2 | 描述 |
+-------------+-----------+-----------------+
For small to medium sized databases, there should be no performance issues at all even using the default database configurations. If you get to huge sizes (where you are counting the database size in terabytes) you can optimize the database in many ways to keep the performance level acceptable.