Database Design: how to support multi

Suppose I have the table:

TABLE: product
=================================================================
| product_id | name           | description                     |
=================================================================
| 1          | Widget 1       | Really nice widget. Buy it now! |
-----------------------------------------------------------------

If I want to provide multi-lingual support, what's the best approach to do that?

Possible solutions:

Add a "language" column to the above table; that'll indicate the language the particular record is. (I don't think this is an option for me since other tables will be using product.product_id as its FK.)

Remove any translatable columns in product table (in the above example, product.name and product.description) and put it in a separate table with a "language" column. That new table will use product.product_id as a FK. (I won't be supporting multi-languages in the first version of my application. This solution means I would have to do an extra JOIN just to get the values of the initially supported language.)

Something else that I didn't consider?

---

I would go with solution 2.

This option will minimize your work for your first version, and will reduce repetition between the columns that do not change.

It does require an extra JOIN , but it is a simple key join so it will not affect your performance much.

---

I would go with a third alternative which is a hybrid of your existing design and solution #2. The columns that exist in your table now represent your "neutral" or default language. You would then add a table for each enttity that need translated values which would contain the PK of the main table, a key for the language code and a column for each value in the parent table that needed a translation.So we might have

Create Table product_translations
    (
    product_id int not null References product( id )
    , language_code varchar(5) not null
    , name ...
    , description ...
    , Primary Key ( product_id, language_code )
    ...
    )

Your queries would then look like:

Select P.product_id
    , Coalesce( PT.name, P.name ) As Name
From product As P
    Left Join product_translations As PT
        On PT.product_id = P.product_id
            And PT.language_code = 'en-UK'

It does mean every query where you pull product information will need a Left Join to the translations table and then decide what to do if there is no translation value: return the default language term (as I have in my example above) or return null.

---

I would use a little modified version of (2). I think it's ok not to remove your name and description columns from product table - in this case you will always have default values for product if a localized version does not exist.

链接地址: http://www.djcxy.com/p/37496.html

上一篇: 语言网站内容来自数据库

下一篇: 数据库设计：如何支持多