Multilingual text records
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:bigyello:)
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:sick:
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!
Re: Multilingual text records
Your design so far is good, and is the kind of thing I would have come up with.
As to the issues you raised:
1) That is a good idea (but you will probably want to display it differently to the user), and maybe deal with the possibility that the default language doesn't have any text.
I would recommend providing an interface for the admin to see the "missing" items along with the same items in the other languages, so that the needed translations can be known.
2) Using a primary key on both fields is the right thing to do, because it ensures that you can't have duplicates for them (which could cause havoc!).
You could use an extra autonumber field, but only to uniquely identify each record - which the primary key would already be doing.
As for the new values for 'ID', if it is only possible for a single user to be adding a new one then you can use a "SELECT Max(ID) + 1 .." query. If it is possible for multiple people to add records at the same time then an alternative (or just more complex) method would be a better idea.
Re: Multilingual text records
Thanks.
Your ideas on (1) are good, I'll take that into account.
As for (2), at the moment it is only one user, but I don't want to assume anything (otherwise I'd just assume there were only ever three languages and I'd be done by now :p) so I want to take into account the possibility that multiple users might be adding records at the same time.
This is a rather common scenario I guess, and 'newbies' solve it by selecting the maximum ID, but of course this gives problems, which are usually solved by... using an autonumber. But I cannot use an autonumber (at least not for the Id as I was using it, right? I could add another UniqueId field or something that was an autonumber but what good would that be, since I still don't know how to determine the next Id?), so I need some other method.
Re: Multilingual text records
If the only way to add a new item is via your software then you can use the Max idea, as long as you ensure that you always create an item for a particular language ID (perhaps a fake one, which the rest of the software ignores), which you also use in the Max query.
Re: Multilingual text records
I'm not quite sure if I get that. What if a user counts the Ids while another user is inserting? They would both count the same Id (right?) and thus both use the same Id in their insert query. The result is that at least two records have identical primary keys (same Id and same LanguageId, for at least one language, probably all languages), so how can this go right?
Re: Multilingual text records
A primary key cannot have a duplicate, so the second Insert would fail (either giving you an error, or check the return value of the function etc you are using).
If that kind of failure occurs, simply repeat the process until it works, or you get a different error, or you get the same number as the previous attempt.
Re: Multilingual text records
Hm I guess I could do that. Doesn't seem extremely robust but I can't think of anything else so I guess I'll take it. Thanks. I'll leave this open for some different input maybe. I just thought back to a class I had in database design like a year ago (computer science is not my field so it was just the one class) and we briefly discussed tables that 'connect' two other tables. They were simply a table with an ID (even optional I think), the ID of some other table, and the ID of yet another table. For example, if you have a table of users and a table of groups, you can put users in groups by a table UsersGroups:
Code:
[ UsersGroups ]
Id
UserId
GroupId
A combination of userId and groupid in this table indicates that this user is part of that group (and thus: that group contains this user).
You must know what I'm talking about. Perhaps it is useful here? It's 0.30am here so I can't think straight anymore :p
