internationalization

[dellsters]

hi,
I need to add descriptive fields in Spanish. How would I model this? Any suggestions from a data modeling best practices standpoint? I was thinking for example a product table. I would add a Product_locale table. I would remove all fields that contains text from the product table and move it to the product_locale table. Here is what I mean:

Before the change:
Product table had: product_id, product_description, category_description, etc.....

After the change:
Product table would have: product_id
Product_locale table would have: product_id, locale_id, product_description, category_description, etc.
It would be a one to many relationship between product and product_locale. Also notice I took out all the descriptive fields from product and put them in product_locale table. Please advise on this design. I would follow the same design for all the tables that need to be translated. Am I better off by having a more genetic design of some kind instead of having to create a new table for each table that need to be translated? Also, would I be better off by putting the english descriptive columns in the main table and just use the locale tables for foreign languages for performance since most of it uses english?

Would the design be different between a transaction data model vs dimensional data model? I actually need it for OLTP, but I got curious about it from a dimensional data model perspective as well.

[TheNJDevil]

It makes much more sense to me to just add those descriptions in each language needed to the dimension table as more attributes.

Example: dimProduct
ProductID, Product_Desc, Product_Desc_ES, Product_Desc_FR, Category_Desc, Category_Desc_ES, Category_Desc_FR

Then create views for each locale.

[dellsters]

That makes sense from a star schema perspective. What about for a transactional system? I know this is the right forum but any advice?

[Sponsored content]