How to update translation table for translation dictionary database

Question

I am implementing a database for a translation dictionary, and am using the design indicated here.

Is there any way to update an entry in the translation table? Or would you need to have a primary key as well in order to facilitate any updates? Ideally, there wouldn't need to be updates, but it is conceivable a translation could be incorrect and need to be changed.

It seems you could delete the incorrect translation and insert a new one. In my case, I have a server DB, and an Android app that will pull down the languages it needs, and the associated words and translations, into a local DB. In this case, while it may be simple to delete the incorrect translation on the server, how would the client know, unless it deleted and repopulated the entire translation table?

Is a primary key, then a UNIQUE constraint on the two word_id columns the best way around this?

David Aldridge David Aldridge · Accepted Answer · 2015-06-22T17:34:09

You can update an entry in the translation table with a statement such as:

update TRANSLATION_EN_DE
set    ID_DE = 3
where  ID_DE = 2 and
       ID_EN = 1;

I would not have one table per language though.

Add a new table for unique languages, and add its primary key to a words table that holds all languages.

Then your translation table would be "word_from" and "word_to".

It will make your design and code much more simple.

To propagate changes to the client you'd probably want to version all of the changes in a new column on all tables to take account of new words/translations, spelling corrections, possible removal of words/translations, and have the client record the version number up to which it has retrieved data.

Since you might have deletes that you want to propagate you'll need to use a "soft delete" flag in the tables, because otherwise there would be no record in the table to hold the version number.

You'd probably also want a table holding those version numbers as a unique key with a text to explain the type of changes that have taken place, and the timestamp for the change. Remove the timestamp columns from all other tables.

So when you make a new batch of changes, create a new version record, make all of the required changes, and then commit all changes in a single transaction. then the entire change set becomes visible to other database users, and they can very efficiently check whether they are up to date or not, and retrieve only the relevant changes.

How to update translation table for translation dictionary database

1 Answers