2

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?

Community
  • 1
  • 1
asorenson
  • 169
  • 3
  • 14

1 Answers1

1

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.

David Aldridge
  • 51,479
  • 8
  • 68
  • 96
  • Sorry, I meant I was using the design referenced in the answer to that question: . I've updated the question to the link as well. So, I have just one translation table, one word table and one language table. – asorenson Jun 22 '15 at 18:13
  • Same principle -- you don't need a single column primary key to be able to modify the translation table, you can modify it based on the uniqueness of two columns. – David Aldridge Jun 22 '15 at 19:03
  • OK, I understand how to modify a row on the server (or anywhere, really), but how does that get propagated to the client? I suppose my question should have been worded differently to indicate this. It is assumed the client will be offline frequently, and will only query the server DB intermittently to download any new insertions or updates. I have update_time columns in each of the tables, but with the translation table, how would the client be able to determine that translation (1,2) now refers to server translation (3,2) and should be accordingly updated? – asorenson Jun 23 '15 at 00:46