Best practice for storing languages in a database

user_9135201 0
Viewed 832

I'm currently working on a project for university. Basically, the task is to implement a basic library management tool (in Java, using the Spring Framework and the Java Persistence API). Part of the software requirement is to be able to add and modify book entries and store them in a database. A book has multiple properties (title, publication date, ...) and also a specific language. Now, my question is: How do I (neatly) implement that whole concept of "language"? I came up with some ideas, each with its own benefits and trade-offs:

Idea 1:

  • User Input: plain text field
  • Database: store language as a string (as an attribute of the book relation)
  • Pros: very simple to implement
  • Cons: languages might not be uniform ("english", "en", "English", "ENGLISH", ...), lots of room for human error (typos, ...)

Idea 2:

  • User Input: drop-down / combo box
  • Database: predefined language relation, containing all possible languages (e.g. language_id as a foreign key in the book relation)
  • Pros: languages are uniform (e.g. "English", "German", "Italian", ...), little room for human error
  • Cons: what is the set of all possible languages? Should/Can some languages be omitted? Is this overkill? Is there too much overhead? What format of languages should be used (human-readable: "English", "english", or more compact: "en", "en-us", ...) ?

Idea 3:

  • User Input: drop-down / combo box
  • Source Code: as in idea 2, but now hardcode all possible languages as enumerations in source code
  • Database: convert the specific enumeration to either a string/ordinal and use it as an attribute in the book relation
  • Pros: pros of Idea 2 + no hardcoding of language strings in source code (from if language = "english" to if language = Language.ENGLISH)
  • Cons: cons of Idea 2 + how are the enumerations mapped into the database (string, ordinal , separate relation)? The enumerations and the languages in the database must be "in sync".

To me, Idea 2 might be the "most-reasonable", but I'm still not sure, whether this is actually a "good" approach. Maybe you can help me out.

jpa spring user-interface database java
1 Answers

Separate presentation from business logic.

Use a standardized language code for your internal business logic and data storage. You have a choice of several. I would choose the code used by Java in its Locale class, if that covers your domain’s needs. For example, en for English and fr for French.

For presentation to the user, localize the display name of each language. When the user chooses a language during data-entry you translate that to a language code value for logic and storage.

Use a GUI widget that lets the user pick from a list. For a long list, usually best to use a widget that allows for type-ahead to pick an item as the user types the first few letters of the name.

Get an array of all known Locale objects by calling Locale.getAvailableLocales. Loop those to make a Set, to build a distinct list of language codes.

Or call Locale.getISOLanguages to get list of all 2-letter language codes defined in ISO 639-1. The Locale class also offers the three-letter ISO 639-2 language code. I am not an expert here, so I do not know the difference. But we can see this list of 3 & 2 letter codes defined by ISO 639-1 and 639-2.

To get localized name of a language, pass the user’s preferred locale object to the Locale::getDisplayLanguage method.

String displayNameOfFrenchLanguageForJapaneseUser = Locale.FRENCH.getDisplayLanguage( Locale.JAPAN ) ;

displayNameOfFrenchLanguageForJapaneseUser = フランス語

And for German user.

String displayNameOfFrenchLanguageForGermanUser = Locale.FRENCH.getDisplayLanguage( Locale.GERMAN );
System.out.println( "displayNameOfFrenchLanguageForGermanUser = " + displayNameOfFrenchLanguageForGermanUser );

displayNameOfFrenchLanguageForGermanUser = Französisch

As for defining enums, an Enum is defined at compile time. An Enum cannot be redefined wih additional or fewer items at runtime. Locales and language codes, in contrast, do change occasionally. Upgrading your deployment with a different version of Java may have changes in its known locales. So I would lean towards soft-coding. If your domain applies to a specific number of languages that are unlikely to change, say the Romance languages of Western Europe, then hard-coding with enum might be appropriate.

If you need to track dialects rather than mere broad languages, such as Québec French versus French in general, then you may want to learn about the Common Locale Data Repository (CLDR) now bundled with Java implementations built from OpenJDK.

user_6427060
Related