Table Design

[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

 

[Damian S]

Hi Chris L,

If there is a one to one relationship you would generally include the data
in the same table. So, for one Item, if there's one English description, one
Japanese description and one Chinese description, you should have them in the
same table.

If, however, you don't have three descriptions, but rather a single
description and you just need to note what language it is in, you could have
something like this:

GlossaryID (autonumber), Item, Description, LanguageID

with a second table LanguageID (autonumber), Language

Where the two tables link on LanguageID.

Hope that gives you some ideas...

Damian.