Hi,

I am working on a website for a friend. The website is very simple, each page consists just of a main title followed by the same 'item' several times. One 'item' is simply a title, a picture and then a description. These items are stored in my database at the moment simply such as this
Code:
[ Item ]
Id (PK)
Title
Description
ImagePath
The idea is that the admin user can add (or edit) these 'items'. He selects an image to upload and types some text for the title and description, and it's added to the page.

The problem is that this text (title and description) needs to be multilingual. At the moment there is only support for one language, since there's just one 'Title' and one 'Description' field for every Item record.

I could go the easy way and 'hardcode' the languages (it will most probably only be dutch, english and german) and use this structure instead
Code:
[ Item ]
Id (PK)   
Title-nl   //dutch
Title-en  //englisb
Title-ge  //german
Description-nl
Description-en
Description-ge
ImagePath
but of course, should a new language be added at a later stage I will have to redo most of the application, since I'd need to modify both the database (add a new field for every text attribute of every table), and the datalayer of the application itself. Not a good idea!


So, I thought I'd make it dynamic and create a Languages table:
Code:
[ Languages ]
Id (PK)
LanguageName
I'd then have one single table Strings that stores all strings, one for each language. For example, it could look like this
(suppose the languages are English (1), Dutch (2) and German (3))
Code:
Id		LanguageId		Text
-----------------------------------------
1		1				Hello world.
1		2				Hallo wereld.
1		3				Hallo Welt.
2		1				My name is
2		2				Mijn naam is
2		3				Meine Name ist
(Excuse me if the german is wrong)

In the Item table then, instead of having a text field 'Description', I'd have a numeric field 'DescriptionId' that points to the Id field in the Strings table.

For example, if the description should be "My name is" in english, then the DescriptionId is set to 2. I could then look up the correct string by
Code:
SELECT Text FROM Strings WHERE Id=2 AND LanguageId=?
where the LanguageId is determined by whichever language the page is currently set to.


This seems like a valid approach to me, but there are two problems that I can see:

1. If the Strings table already has some records, and then I decide to add a language, then this language will be missing from the existing records. However, I could simply use a 'default' language (LanguageId=1) if the LanguageId doesn't exist. For example, suppose I query for Id=5, LanguageId=4, the record doesn't exist and it looks for Id=5, LanguageId=1 instead to get the default (english) translation.
That seems like a valid solution, but are there better solutions?


2. How do I even make this database structure (the Strings table) ? I'm not very experienced with databases, and most experience I have is from an existing database so database design is not one of my strong sides

Of course the Id of the Strings table is no longer a primary key (as it is duplicated for every language), but the combination Id and LanguageId is unique so that could be a primary key. My question now is: can I still use auto numbering in this case? How do I decide which value to give 'Id' when adding a new string?

(I am using Access if that matters).

Anything else that I need to take into account? Thanks!