dimanche 12 juin 2016

Multilanguage multientry database design

I'm having some trouble with ecommerce product database design.

There can be dozens of languages and dozens of different fields (name, description, meta_description, meta_keywords, slug, you name it).

Does the 4th option have a huge performance hit or can it stay fast when there's hundreds of thousands rows in the translations and products tables?

1.

products_table
id, name_en, name_es, description_en, description_es, price

SELECT * FROM products_table;

2.

products_table
id, price

translations_table
id, product_id, name_en, name_es, description_en, description_es

SELECT * FROM products_table AS p
JOIN translations_table AS t ON (p.id = t.product_id);

3.

products_table
id, price

translations_table
id, product_id, lang, name, description

SELECT * FROM products_table AS p
JOIN translations_table AS t ON (p.id = t.product_id AND t.lang = 'en');

4.

products_table
id, price

translations_table
id, product_id, lang, type, value

SELECT p.*, t.value as name, t2.value as description FROM products_table AS p
JOIN translations_table AS t ON (p.id = t.product_id AND t.lang = 'en' AND type = 'name');
JOIN translations_table AS t2 ON (p.id = t.product_id AND t.lang = 'en' AND type = 'description');

5.

products_table
id, price

names_table
id, product_id, lang, value

decsriptions_table
id, product_id, lang, value

SELECT p.*, n.value, d.value FROM products_table AS p
JOIN names_table AS n ON (p.id = n.product_id AND n.lang = 'en');
JOIN descriptions_table AS d ON (p.id = d.product_id AND d.lang = 'en');

Aucun commentaire:

Enregistrer un commentaire