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