I've got a simple database of products.
Each product is N:1 related to several attributes, such as material, gender, shape and so on.
Each attribute is a table (e.g. table "shapes" with square, round and other rows)
Now I'd like to "translate" each attribute, for example, I want the "square" record, to be associated with "quadrato" (it), "carré" (fr) and so on. "languages" is another table (it,fr,en) and languages can be added or removed, so having columns "it_value", "fr_value" is obviously impossible and ugly.
I'm thinking about
Do you think other solutions could be more efficient or more elegant than that?
(Sorry for not posting an ER diagram too, but I'm not a so 'graphic' person! ;))
How similar are the shapes/materials tables going to be? Could you not accomplish that with a single table with a "type of attribute" (shapes/materials) and "name of attribute" (square, cotton, etc...) fieldset?
If that's the case, then I'd probably use something like:
items: id, name
languages: id, name
items_translations: item_id, language_id, name
The 'name' in the items table would be the default language (e.g. english) of your system, and any translations would be in the items_translations table's 'name' field.