How to prevent duplicate entries in a table ?

habbardone

I have written a script to update the database table with
different phrases in different languages.

The user first selects the languages he is working with and then enters and
then he enters the phrase in the base language.

The script checks to see if the phrase already exists in
the base language and if so, it displays it for editing.

If not existing, it creates a new row.

The problem is that when the phrase exists but the language the phrase is in is not the chosen base
language, we get this situation:

[ATTACH=CONFIG]5357[/ATTACH]

Here, the base language was English and "accept" was inserted with Spanish(eur), French, Italian and Turkish as
the additional languages.

Next a Mexican entered "acceti" in the Spanish(South Am) with English as the additional language.
Next a Portuguse entered "aceite" in the Portuguese with English as the additional language.

So we get three rows of "accept" instead of one.

What do you think is the best ( and most efficient )
way to avoid this ?

I am concerned to do it efficiently because we will have over 30 languages and a lot of data
pretty soon. :rolleyes:

Thanks.

database1.jpg

laserlight

The most immediate way to solve this is to alter the base language phrase column to have a unique constraint, and then when the user enters the phrase in the base language, do an AJAXy check to ensure that it is not a duplicate, and if it is, notify the user. If the user insists on continuing, at the time of submission, instead of trying to create a new row (which will result in a unique constraint violation), either merge with the existing row if feasible, and if not feasible stop and inform the user about the conflict.

That said, I wonder how you plan to handle the fact that the same word in the base language may have different translations based on parts of speech classification and contextual usage.

Also, note that while it may look easy to have a column for each language (and dialects, etc, thereof), a more normalised design would have three tables: one table that lists the phrases to be translated (in the base language), one table that lists the languages (e.g., language names and codes), and a join table that actually holds the translations. This way, adding a new language means adding a new row, not a new column.

habbardone

Yes, my difficulty is also that therer is no set base language.

The base language is set by the the user.

So if we have 30 languages, the user can choose any 6 to work with.
The idea is that the base language is the one they know well.

So we could have someone enter the word meaning "accept" in Portuguese and Turkish
and someone else separately enter "accept" in Punjabi and Spanish ( with no entry in the English)

so we have "Port_accept"---"Turk_accept"

and "Punj_accept"---"Span_accept"

There would be no way to connect the rows until they get a common language entry eg Italian

"Ital_accept"---"Span_accept"

and

"Turk_accept"---"Ital_accept"

Now, we have four different users that have made entries and
they can be linked, but not at the time of input.

Perhaps it would be best to have a chron job search and merge the rows once a day ?

I think you are correct about needing to change the table structure.
Adding a new language as a row would be best.

Not quite sure about this. 😕

laserlight

habbardone wrote:
So if we have 30 languages, the user can choose any 6 to work with.
The idea is that the base language is the one they know well.

Out of curiosity, but what is the purpose of this project? Are you familiar with the more typical kind of website translations that might be done with the help of a tool like gettext, either with the aid of dedicated translators, or with tools like Transifex?

habbardone wrote:
Now, we have four different users that have made entries and
they can be linked, but not at the time of input.

Perhaps it would be best to have a chron job search and merge the rows once a day ?

Different humans -- or even the same human at different times -- can and do translate the same word/phrase differently even when it is in the same parts of speech category and in the same context, so you are certainly going to run into conflicts that cannot be automatically resolved (i.e., for the given word/phrase in the given language, there will be multiple rows with differing values).

Related to this and to the purpose of your project, the idea of merging translations of words/phrases seems rather naive. How do you deal with the same word having multiple possible meanings and hence translations? You could end up matching a word with one meaning with the same word with a different meaning, leading to hilarious translations. This is why translators often need to know the context if they are translating something shorter than a paragraph (or at least a sentence). If you are just letting people provide words to be translated at whim, then somehow they need to provide the context, but if you make the context open-ended, then you're in the realm of heuristics, machine learning, etc, in order to make correct matches.

habbardone

Yes, and there are many more descriptive words in English than there are in say Turkish.
So if you want a translation of obtain, get, acquire, take, gain it will be almak for all of them.

The idea of the database is to create flash cards for learning
- it is not to translate anything, however the phrases should match up.

Therefore 'obtain' will have to have 'almak', and so would 'take' have 'almak', definitely multiple entries.

so if four different users create the above rows, it would be best to give the
last users the chance to put them on the same row ( if the meanings are really the same )

So the phrase "I like apples" if entered in different languages can end up on he same row.

You are right, a human judgement is likely required, but making it easy to do
by presenting similar entries ( like the google search maybe ) would make it doable.

So 'I took four apples' and 'I obtained four apples' will have to have the same same Turkish
phrase .. 'Dort elma aldim'.

I am thinking that it may be better to have a fixed common language (English) that
always must to be entered.

That way only one column needs to be searched for similar entries.

So ... when someone enters "I like apples" in the English box, what is
the best way to search the column for similar entries ?
.