Best Logic for Processing Master-Detail Records

Question

Let's say, we have these SQL tables:

[Articles] 
bill   int         (pkey)
arti   int         (pkey)
name   varchar(50) 

[Bills]
bill   int         (pkey)
fdate  date
uid    int

Let's suppose we have a list of items on a grid, representing a bill:

--------------------------------------------------------------
Id[      15]  Date [01-01-1980]
User [pepe]

Code   Name
----------------------------
1      Something
2      Article name
3      lolololololoolo
4      datadatdatdatdata
5      datadatdatdatdata
--------------------------------------------------------------

So, we have a header with, an id, user, date, etc. And, then, the grid filled with items.

Both tables will be saved at the same time when the user decides (a save button). And, the user can also see a previous bill and modify it. So, what's better?

1. Loop the items and make a query to decide: if exists is an INSERT, else, is an UPDATE.
2. Delete All the items (by bill id) and then, do all INSERTS.

Answer 1

Why don't you use the MySQL Syntax ON DUPLICATE KEY?

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

 INSERT INTO table (a,b,c) VALUES
 (1,2,3)   ON DUPLICATE KEY UPDATE
 c=c+1;

Answer 2

Neither

• Extract the grid data to XML, or create/load 2 temp tables
• Parse the XML in SQL into a temp table, or read the 2 temp tables...
• Update or insert (MERGE, ON DUPLICATE etc as needed)

Wrap the writes to both tables in a transaction

Answer 3

If you only have the two choices it will mostly depend on your requirements and the use cases you want to optimize for. That said you should consider the following

Performance

In Option 1 Deleting all of the articles will take longer.

In option 2 Adding no articles to an bill with 10 article will take the same about of time as adding 10 articles to a bill with no articles.

Auditing

Option 2 is very difficult to audit

Concurrency Assuming no concurrency detection in application

Two users open the bill at the same time. Each user adds adds five articles and hits save.

In option 1 you'll end up with 10 articles. In option 2 you'll end up with five.

I can't say which is right.

Transaction Performance

When adding articles to existing bills Transactions will take longer than need be in option 2. This increases the likelihood of deadlocks for that use case.

Save failures without transaction support this assumes your app doesn't use transactions.

In Option 1 there's a potential that new articles may be lost and deleted articles that should have been deleted aren't

In Option 2 there's a potential for all articles to be lost