I have some doubts about writing records in tables from N to M.
InmybankwheninsertingadoctorIinsertinthe"Doctor" table the registration of Doctor, and in "Doctor x Specialty" the specialties of this doctor. Registry modification:
Solution 1 - In a normal scenario, I would update the "Medical" table and after that I would choose to delete all the records of "Physician x Specialty" and then do the insert of the entire list of "Physician x Specialty".
As I'm applying Hibernate to the project and had little contact with it, I asked for help from a co-worker. He said that my "solution" would be very wrong. Because iterating over each item would have a lower cost than iterating over working with the entire list. His solution would be as follows:
Solution 2 - I fill in an auxiliary list with my specialties already registered in the database (usually this is done in the registry search, so I already do it anyway). In the change I would scroll EVERY list and check each item. If the item was deleted, I would do the deletion 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 execute anything.
Note about Solution 1 : In my opinion (which may be wrong) solution 1 would be ideal, since I would perform all the steps in a single connection and for a very large list would save a lot time and memory.
Comment on Solution 2 : The colleague who advised me that I would use a lot of memory using a connection to work with the entire list (????), but I have many doubts. The pro is for small list, but even so I see many back and forth in the database and to my mind this is bad. As I'm working with Hibernate and I have little knowledge, I'm pretty much in doubt about what to use.
What I had already thought was if there was a change in the list, I would do the deletion and re-insert the records. In this scenario what would be the best practice?
Comparing the new and old specials I would have to iterate over the whole list and then do the insertion and deletion.
Even though the input and output 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. But in a larger entry the complexity is exponential.
Well, as far as I remember about algorithm complexity this would require a lot of time and memory. But my friend says that's despicable. Here is my doubt. Thinking that I do not know the size of my table of specialties, what is the best way to implement ...