Re: table design for Globalization in Oracle ERP

Thanks for your feedback.

We use AL32UTF8 character set.

My question is not detail. What I want to ask is the table design.

There are two ways to design table to store the translation data.

1. The first way is to put translation table into separate table. The advantage is that no data redundancy. And does not affect existing tables. The disadvantage is that we need to join table when get data.

CREATE TABLE product_information

( product_id NUMBER(6)

CREATE TABLE product_descriptions
( product_id NUMBER(6)
, language_id VARCHAR2(3)
, translated_name NVARCHAR2(50)
, translated_description NVARCHAR2(2000)
);

2. The second way is not to put translation table into separate table. The disadvantage is that has data redundancy, and we need to sync the data, and change existing tables primary key. The advantage is that we don't need to join table when get data

CREATE TABLE product_information
( product_id NUMBER(6)
, language_id VARCHAR2(3)
, product_name VARCHAR2(50)
, product_description VARCHAR2(2000)
, category_id NUMBER(2)
, warranty_period INTERVAL YEAR TO MONTH
, supplier_id NUMBER(6)
, product_status VARCHAR2(20)
, list_price NUMBER(8,2)

);

Your answer is high appreciated

Thanks
Jacky

John Hurley wrote:

> On Aug 25, 8:02 am, lsllcm <lsl..._at_gmail.com> wrote:
>
> snip
>
> > Hi All,
> >
> > I have one question about table design for Globalization in Oracle
> > ERP. The following example is from Oracle ERP. Do I have other chocies
> > to design table for this kind of requirement.
> >
> > CREATE TABLE product_information
> > ( product_id NUMBER(6)
> > , product_name VARCHAR2(50)
> > , product_description VARCHAR2(2000)
> > , category_id NUMBER(2)
> > , warranty_period INTERVAL YEAR TO MONTH
> > , supplier_id NUMBER(6)
> > , product_status VARCHAR2(20)
> > , list_price NUMBER(8,2)
> > );
> >
> > CREATE TABLE product_descriptions
> > ( product_id NUMBER(6)
> > , language_id VARCHAR2(3)
> > , translated_name NVARCHAR2(50)
> > , translated_description NVARCHAR2(2000)
> > );
> >
> > Thanks at first
> > Jacky
>
> Depending on what you select for database characterset VARCHAR2 and
> NVARCHAR2 may or may not be different.