Updating a M to N relationship:which is the best method ?

Wentu

Hi !

I though this was a sort of FAQ but couldnt find a decent answer.
Let's consider a stupid 😃 example:

I have a table A that is a list of sentences:

TableA
Sentence_id - Sentence
1 - "Hi"
2 - "Hello"
3 - "How r u ? "
....

And I have a table B with people's name

TableB
PPL_id - Name
1 - "John"
2 - "Mark"
3 - "Luke"
...

Every person is asked everyday to choose (in a form with checkboxes... ) which sentences he used. The information about this is stored in an association table C

TableC
Sentence_id - PPL_id
1 - 2
1 - 3
2 - 1
2 - 2
2 - 3
3 - 1
......

Now my problem is: since these relationships could change often, which is the best way to update this table ?
For instance: John, the number 1, has associated the sentences 2 and 3. He fills the form today and changes to 2 and 4.
Do I :

1) Erase from table C all entries with PPL_id = 1 and then insert two new rows, with PPL_id = 1 and Sentence_id = 2 and 4 ?

2) do I query the DB for the old entries with John , check if they are also in the new choice, if so, keep them, else delete them. And finally add all the new choices.

The 1) seems good if there are few sentences choosen or if there are many changes from the first choice to the second. But say there are 1000 sentences checked and John changes only one: i erase all and have to re-insert 1000 entries. It seems absurd.
The 2) seems better if the choices of John are almost the same but there are a lot of checks to do.

Are there better way to update an association table in a case like this one ?

thankx a lot !!

Wentu

tsinka

Hi,

I don't really know if this is a better solution but it might be faster:
But it only works with MySQL, I don't know if other DB's have a similar feature:

First create a unique key including both fields in TableC.

Then do the following queries:

1) delete all records from C:

DELETE FROM tblC WHERE PPL_id = <theid> AND Sentence_id NOT IN (<list of chosen sentences>);

<list of chosen sentences> = 5,4,3,2,10,2,5

With that you removed all sentences the user didn't choose.

2) Then insert the chosen one and make MySQL to ignore the once that already exist with IGNORE:

Example:

INSERT IGNORE INTO tblC VALUES(3,5);

By creating an unique key over the two fields in tblC MySQL will ignore each INSERT statement that would duplicate a combination of PPL_id,Sentence_id.

The unique key would generally prevent the insertion of duplicate value pairs.

Thomas