Hi All,
I need design a data base that support many languages (locales), and I tried find same samples that show how do this, but I can’t found any ER sample, or article that show best practices to do it.
I’d created one model of my mind, but I don’t know if it is correct.
Anyone can help-me, and give me were places I can found this information.
Thanks
Registered to reply to your post.
I've spent most of today asking similar questions to the ones you are asking, and looking for answers.
I looked at your design and it looks very similar to what I am starting to think is the best approach.
In particular, I am trying to represent countries, cities, etc in multiple languages.
It is important to not lose data integrity for the sake of internationalization. For example, cities in multiple languages must still be identifiable as referring to the same city.
You must have a way of uniquely identifying every entity (country, city, whatever you want) without using a specific language. ISO codes, when they are available, do this nicely.
I think your two-table system of listing every entity with a unique identifier in one table, and linking names of those identities with a second table, is a bit awkward but probably the best approach.
So basically my contribution here is that I think you are on the right path, and I want to encourage you and others with similar questions to consider this design carefully.
My only criticism / piece of advice is that as you advance from most general to most specific (country -> province -> city) you are adding unnecessary keys that are going to complicate your design, and fail you in your quest for normalization.
Take 'cities' for example. A city exists in a province (one and only one province) which exists in a country (one and only one country).
If your city links to a province, and that province to a country, then they are all linked together.
There is no need for a city to link to a country; that is redundant information, and possibly dangerous as you may have different values in the two locations.
Furthermore, along the same line of reasoning there is no reason for any of your names tables to have links to anything other than the exact entity that they are linked to (as well as the language they are in).
ProvinceNames should only have a link to a province (not countries), and CityNames should only have a link to a city (not a province or a country).