Localized CMS/Shopping Cart Database

[theEclipse]

I am in the process of reviewing the designs of some third party (ie. some open source) databases that handle CMS and Webstore information in an attempt to choose one to fork into a standardized code base for the company I work with.

One thing that I have seen is a varied approach to localizing the Content inside the database. I have seen basically two differing approaches and I am curious as to which is 'better' from a database design paradigm.

The first uses a localization table for each table which would be localized:

Table languages(id int(11) unsigned[...]);
Table Products(id int(11) unsigned[...]);
Table Product_descriptions(language int(11) unsigned, product int(11) unsigned, description text(255));

Table Categories(id int(11) unsigned [...]);
Table category_descriptions(category int(11) unsigned, language int(11) unsigned, description text(255));

etc etc

Another approach uses a centralized localization table or set of tables:

Table languages(id int(11) [...]);
Table short_strings(language int(11), id varchar(32), string varchar(75));
Table long_strings(language int(11), id varchar(32), string text);

Table products(id int(11) [...], name varchar(32), description varchar(32));
Table categories(id int(11) [...], name varchar(32),
description varchar(32));

Where the varchar(32)s are MD5 hashes or similar UUID.

Supposedly the advantage to the first approach is that all the info for products is contained in tables relating specifically to products and categories to categories.

Supposedly the advantage to the second approach is that all the localization data/strings is in two tables. Usually in these designs nearly every table in sight has at least one or more links to these localization string tables.

Both of these approaches leave something to be desired, but that might just be because there isn't any simple solution to this problem.

Is there a standard or more accepted way of accomplishing this?


Robert Carpenter
Remember....eternity is much longer than this ~80 years we will spend roaming this earth.
ô¿ô

http://www.robertcarpenter.net

 

[TrojanWarBlade]

Personally I'd lean towards the latter because invariably, new languages get added over time and it's much easier to dump the native language (usually English) from one table for the translators to translate than to have to dump fragments from many individual tables and later re-import to each of them keeping track of what came from where with no mistakes.

I'm sure there are arguments both ways but in my experience I have had to do this and been glad that our design allowed dumping from one table only.


Trojan.

 

[theEclipse]

TWB -

Thanks for the reply. In researching this topic further I've been re-reading some old database books and thinking about the theory of a relational database design (no...really). For a full third (I think) normal form each table should represent one (and only one) idea and all necessary and relevant information pertaining to that single idea.

Furthermore, from what I can tell, its also considered bad design to have multiple tables signifying the same idea.

I've decided to take the unified table approach for the reason you listed as well as the above two.



Robert Carpenter
Remember....eternity is much longer than this ~80 years we will spend roaming this earth.
ô¿ô

http://www.robertcarpenter.net