Best Scenario: Recording n table for n Hibernate

Question

I have some doubts about recording records in tables from N to M.

My scenario is this::

In my bank when I insert a doctor in the table "Doctor" the medical record, and in "Doctor x Specialty" the specialties of this doctor. Change of registration:

Solution 1 - In a normal scenario, I would update the "Doctor" table and after that I would choose to delete all records from "Doctor x Specialty" and then make the Insert from the entire list of "Doctor x Specialty".

As I am applying the Hibernate in the project and had little contact with it, I asked for help to a co-worker. The same said that this my "solution" would be very wrong. Because iterating over each item would cost less than iterating over working with the entire list. His solution would be the following:

Solution 2 - I fill an auxiliary list with my specialties already registered in the database (usually this is done in the search of the record, so I already do this anyway). In the change I would go through the list and check each item. If the item was deleted, I would delete it in the database. If the item was inserted, I would do the Insert in the database, if the item remains on the screen, I would not run anything.

Comment on Solution 1: In my opinion (which may be mistaken) the solution 1 would be ideal, because I would perform all the steps in a single connection and for a very large list would save a lot of time and memory.

Comment on Solution 2: The colleague who advised me said that I would use a lot of memory resource using a connection to work with the whole list (????) but I have many doubts. The pro is for small list, but still I see many back and forth in the database and in my view this is bad. As I am working with Hibernate and have little knowledge, I am very doubtful about what to use.

What I had already thought was if there was a change in the list, I would delete and re-enter the records. In this scenario what would be the best practice?

---

Compare the new specialties with the old ones I would have to iterate over the whole list and then do the insertion and deletion.

Even if the entry and exit in the database is done in only one command (DELETE IN) the iteration on the list is totally hostage to the size of my input (Logically). In an entry of 10 specialties is Ok. However in a larger input the complexity is exponential.

Well, as far as I can remember about the complexity of algorithms, that would take a lot of time and memory. But my friend says this is despicable. There is my doubt. Thinking that I do not know what will be the dimension of my table of specialties, what is the best way to implement...

Answer 1

Well, if I understood your case correctly, based on similar experiences, you could put it like this: In a doctor update and their respective specialties, you can make an algorithm to compare the new specialties with the old ones:

which were not excluded among the new;

I have excluded from the old what is not in the new; and

adds new specialties that did not have among the old.

Do you understand? So you don’t need to delete them all and reinsert everything. Another observation would be to remove the id table medicoXespecialidade, because you can make a joint primary key of id_Medic and id_Especiality.

I hope I’ve helped.