Re: [libreoffice-users] Currently Using Spreadsheet for Personal Project - Thinking About Database

Hi Mark,


On Tue, 12 Aug 2014 22:37:18 +0100
Mark Bourne <libreoffice-ml.mbourne@spamgourmet.com> wrote:

[snip]

> > Also, then you can localize your status names if you ever want to
> > make your program support other languages.
>
> Something else I haven't had to worry about, but if I did I'd have
> done the localisation in the application - the enum values (as I use
> them anyway) are not generally displayed as-is to the user anyway,
> they're just convenient names to use in the database. Using localised
> status names in the database table surely hinders localising the
> application - if you wanted to connect both English and French
> front-end applications to the same back-end database, wouldn't they
> both get the same status names to display, rather than the names
> appropriate for the application language? Or would the status table
> in that case contain strings in one language (or perhaps even generic
> keywords) for which the application would look up translations in
> it's own localisation table?

Normally, localization is done by having a plain text file with all the
application strings in it, and this file is simply copied and each
string translated for each different language. Then at run time the
application knows which locale it is in, and looks in the appropriate
file for all the translated application strings. But they can just as
easily be stored in the database, though.

(Sorry, I know you probably know that, just including it all for
anybody reading that isn't as familiar with this stuff.)

Now, I've never actually dealt with localization myself, so I'm
guessing a little, and not sure what might be considered best
practice out in the wild. But obviously you wouldn't want to add a new
field to each table for each language, although you might if you were
only dealing with a small, restricted set of languages. But generally
you want that sort of stuff stored as data, not as structure, because
it's easier to add rows than it is to add fields. So I'm guessing one
solution would be to add a language code field to the lookup table, and
make a composite primary key of that and the ID. Then you can enter one
row for each status for each language.

But generally I think composite keys are considered bad. Although maybe
not, but I don't like them, at least. So another approach is to link on
something other than the primary key. One thing we often did with
lookup tables was have a short code and a description for each, so for
purposes of the application you would use the code, but you could
display the description to the user. And use whichever one fit better
for printing. So you could use the short code as the foreign key from
whatever table references the status table, instead of the ID. Then the
status table could still have a single primary key field of the ID, and
just a unique constraint on the short code and language fields. Again,
one entry for each short code and language pair, with the translated
description. The disadvantage of this is not having the primary key of
the lookup table as the foreign key of the referencing table.

For me once you have a database, there are other questions, like what
are you storing in the database (user entries as opposed to programmer
entered things like statuses)? Do those need to be translated?
If they do, then you've got a whole different set of problems, but
assuming they don't, then you only have to translate the scaffolding
stuff.

I'd want to keep the translation of database stuff in the database. At
least then it's easier to update if you ever do need to make changes.
Just add a status, and add all the translations for it. Otherwise, if
it's an enum, and then the translations are in the frontend, you need
to not only update the database structure to add an enum, you also need
to update all the frontends to have the new translation (even if that
doesn't actually require a recompile).


[snip]

-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted