Assume I've got a table named bookLibrary where the unique id of the book is related to numerous libraries (by their own id). Should you get an update for any book as a range of the libraries where a book is located, what's the most effective method of update the bookLibrary table.

Presently, I simply remove all of the rows that report towards the book and give a new relationship (row) for every library within the array which i receive. This appears a little drastic, but it works also it makes certain that you will find no duplicate rows within the table.

An alternate approach is always to fetch all of the rows for any book and have a look each and every row, that's, (1) may be the row still valid (may be the book still in library x), (2) what rows have to be placed (have new libraries been added for book x), and (3) which rows remain unaltered.

It is a trivial trouble with a simple solution, but an issue such as this makes me frequently question about efficiency i wish to make certain that annoying bugs (like duplicate rows) are prevented whenever possible.

either approach works. I'd choose the 'delete then reinsert' option unless of course there is an established performance bottleneck that way, since it's simpler in the server perspective. Also, there's no guarantee that loading the rows and doing server side evaluations could be much better, as you have to load the rows, perform the in-code information, then resave as much as n rows.

To answer your question, you should perform some profiling to determine the way the approaches compare. A great deal would rely on the amount of rows, the amount of posts within the rows, the sql involved, and perhaps the way the sql claims are delivered to the server.

Regarding reliability, you'll be able to mitigate errors with either approach via extensive tests. I believe the 'delete then reinsert' approach can also be better to test.