Whats the best way to desig a multilingual database ?

[MONO]

Hello!

I´m designing a database with "products", "categories" and want to be
able too show the user category names i choosen language(ENGLISH,
SWEDISH and so on).

I have come up with a couple of ways to solve this but whats the best
way?

Shall i use a lookup table outside the relation whats best?
I´m using Access 2000

My Tables

--------------
Manufactor
--------------
intManufactorID
strManufactorName

--------------
Product
--------------
intProductID
strProductName

--------------
Category
--------------
intCategoryID
strCategoryName

--------------
Language
--------------
intLanguageID
strLanguage


One way to do this (i don´t know if this is the right way)
--------------
Product
--------------
intProductID
intManufactorID
intCategoryID
ProductName

----------------
CategoryLanguage
----------------
intCategoryLanguageID
intCategoryID
intLanguageID
strCategoryName

----------------
Language
----------------
intLanguageID
strLanguage

If I do this way HOW should i make the ralation between "inCategoryID"
in Product to "inCategoryID" in CategoryLanguage.?

 

[TC]

If you have categories:

Category:
intCategoryID (PK)
strCategoryName

and languages:

Language:
intLanguageID (PK)
strLanguage < I assume this is the language name.

and you want to display the category names in different languages, you
really need:

Category:
intCategoryID (PK)
(NO strCategoryName)

Language:
intLanguageID (PK)
strLanguage

CategoryName:
intCategoryID ( composite )
intLanguageID ( primary key )
strCategoryName


So if you had:

Category:
11

and:

Language:
EN English
FR French

You might have:

CategoryName:
11 EN Pen
11 FR Plume (French for pen?)
etc.

Mind you, I'm sure there may be issues in displaying different language
characters on the scren. I don't know anything about those issues.

HTH,
TC