0

I've been reading some articles about usage of composite keys in MySql and found that a composite key can't own a auto_increment id column. However, I'm interested in using a similar feature. Let's explain it:

Using MariaDB 10 (InnoDB) and Hibernate 3.6.9

I want to do some of my application table fields translatable. I have thought an only table for translations should be enough. This table has a composite key which has an int value as a key for the translation and also the locale value for the concrete text. The same id and locale values can't place as entries.

So that's how the model should look like:

enter image description here

I don't want the translations to be loaded with each of the random entities as a Collection, I'm thinking about a method like String translationFor(Integer id, Locale loc) could do it for my current locale. However, when I save some translation Set I want to assign them the same id. Let's take this case:

  • Spanish: Cuchara
  • English: Spoon

The table should look as:

id  locale  translation
1   es      Cuchara
1   en      Spoon

But I can't tell MySql to have a composite id with auto_increment column. So, I consider I should assign it manually, performing these steps:

  • Build the Translation entities with the locale values
  • Begin a transaction in Hibernate session
  • Retrieve the last id value in the translations table
  • Assign it manually to the entities
  • Save them
  • Commit the transaction

Is it the most proper way? Am I doing it atomically?

Community
  • 1
  • 1
Aritz
  • 30,971
  • 16
  • 136
  • 217

1 Answers1

0

I assume you are planning on having multiple tables needing the translation of 'spoon'? If so, let me move your focus away from id.

The translation table needs PRIMARY KEY(code, locale) where code is what you have as some_translatable_value in `random_table_1.

code could be the string (perhaps abbreviated) in your favorite language. Note that if you later change the phrasing of the text (to "silver spoon"), do not go back and change code; it can stay the same ("spoon").

I do not know whether you can achieve this in Hibernate; I am not fluent in that. (I tend to avoid 3rd party packages; they tend to get int the way.) If Hibernate forces you to have an AUTO_INCREMENT id on each table, so be it. It will be a harmless waste. You should then declare the pair (code, locale) as unique (in order to get the desired index).

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Thanks for your answer rick. My aim is to have the translation table linked to any table which needs translations, you're right. However, I don't see the point in your proposal. You're basically suggesting to change the id for a string, but that would force me either to let my controller generate it or to have the end user typing it. :-( – Aritz Mar 30 '15 at 21:20
  • If you intend to _share_ translations, such as needing "spoon" in multiple places, then you need some key to fetch Cuchara/spoon/etc. One way is to have an INT, but that is not very human friendly. Another way is to invent some codes (eg, 'spoon') with the hopes of remembering "I already have spoon in my translation table; I don't need to enter it again." – Rick James Mar 30 '15 at 23:23
  • If you never (or very rarely) need the same translation in multiple places, then my comment about `code VARCHAR(...)` is irrelevant; stick with an `int`. – Rick James Mar 30 '15 at 23:24