Table Design Mult Language

[Chris L]

Hi,

I'm trying to make a Glossary database in 3 languages i.e Eng, Jpn, Chn.
Currently I have 3 tables namely tblGlossary, tblUser, tblChangeHistory
and the tblGlossary contains the following fields:
GlossaryID(primary key), Item_Eng, Item_Jpn, Item_Chn, Desc_Eng, Desc_Jpn,
Desc_Chn, Classification

The tblGlossary table design somehow contains a lots of field and will
probaly consumes a lots of memory eventhough the field in Japanese or Chinese
is empty.
So I'm trying to seperate the table into 3 different tables for efficiency
purpose i.e.

tblGlossaryEng: GlossaryID(primary key), Item_Eng, Desc_Eng, Classification
tblGlossaryJpn: GlossaryID, JpnID(primary key), Item_Eng, Desc_Eng,
Classification
tblGlossaryChn: GlossaryID, ChnID(primary key), Item_Eng, Desc_Eng,
Classification

But I'm stuck with the synchronization of the GlossaryID which should be
unique and somehow there are many duplicates in the 3 tables.

Should I stick with the first table? What are the drawbacks of having a big
table aside the memory consumption? I'm using select query to seperate the
different languages from the tblGlossary, will it affect the scalability or
further expansion in the future?

Thanks & Regards,
Chris

 

[KARL DEWEY]

One way is to add a foreign key to each glossary as the many side of a
one-to-many relationship. Your tblChangeHistory table could be the one side
of the relation. tblChangeHistory would have WordID (primary key),
ChangeHistory (memo field).