Probably a pretty straightforward question but I'm a little stuck on how to proceed. I am creating a new table for a quoting system. Nothing too special and have used a fairly typical design:

QuoteHeader (PK(QuoteID):CustomerID:CreationDate:etc etc etc)
QuoteDetail (PK(QuoteID:Line#):ProductID:Quantity:Price etc etc )

This is pretty close to the usual Order/Order Detail example given just about anywhere. My problem is with the Primary Key in the QuoteDetail table. Say you had the following data:

Record 1: QuoteID: 10001 | Line#: 1 etc etc
Record 2: QuoteID: 10001 | Line#: 2 etc etc
Record 3: QuoteID: 10001 | Line#: 3 etc etc

Now in the past we've always used pessimistic concurrency control so if we were to delete Record 2...the database would simply be updated as below:

Record 1: QuoteID: 10001 | Line#: 1 etc etc
Record 3: QuoteID: 10001 | Line#: 2 etc etc

Probably not great as this required a change to the actual primary key value which is kind of a no-no.

For the quoting system though we are going to use optimistic concurrency control, utilising a timestamp field. Obviously performing the above operation on a delete would cause things to go haywire. Record 3 would now be considered to be the "same record" as record 2. Anyone who happened to be working on record 2 (the deleted one) at the same time would be trying to update Record 2 which would now be actually referencing Record 3. The timestamp would stop them but really the two users should have been working on completely different records.

As I mentioned, this design comes up a lot in examples but am I misunderstanding the Line# field. Is it really more or a LineID field which bears no physical relation to the "Line Number" the detail line takes in the quote. If I approach things that way I can then simply delete by primary key with no need to keep Line Numbers sequential, with the added bonus of allowing the Primary Keys to become immutable. I'm not using a QuoteID/ProductID as the primary key as multiple lines can exist for the same product in a quote depending on the quantity. And I'm not using QuoteID/ProductID/Quantity as the primary key as the quantity value can obviously be changed. So am I on the right track here? Should I consider the Line number really be a unique identifier? And if so should I be using an auto-incrementing value or something like a GUID?

Cheers,

Steve

Sorry, and one more thing? Would it be better for the LineID number to be unique just for each QuoteID or unique across all QuoteIDs? And depending on which one, why?

Use surrogate keys in your design to keep the identity of the record separate from its logical presentation order.
If line number merely indicates the order in which the line should be displayed, then this value can be calculated dynamically when a report is run.

Cheers mate,

What to be people recommend to do when using a surrogate key in this situation?

QuoteDetail(PK (QuoteID:LineID):etc etc etc)

or

QuoteDetail (PK (QuoteLineID): QuoteID: etc etc etc)

I've always leaned towards the first type but I've seen quite a few examples of the kind below. If anyone out there prefers the bottom type I'd be interested to hear they're reasoning/advantages for using that kind of PK design?

I suggest that you look at how large and rugged databases (particularly Oracle - in my experience) does it. It answers your question.

The deleted record is written to a redo log and once committed, written back as "dirty buffer" to the redo log. (Dirty buffers are flushed to the disk at some other interval and finally when "shutdown"ed).

The "other" transaction, if it didn't acquire a lock on the entire quote (quote#1), scans the redo log and if not dirty proceeds by acquiring the appropriate locks.

For such an application, I would go with Oracle and if cost were a criterion, then Oracle 10G Express which is free and has the same data base engine.

Regarding the second question, (whether line# should be a fresh series for every quote), if you are writing a package, i suggest you rovide both options chosen at install time. THus users can go with their existing practice and choice.

End

Option two, for me. If you use natural keys then you are going to have to have composite primary keys eventually, but one of the benefits of surrogate keys is that you can avoid such annoyances.
...and I'd name it QuoteDetailID, to match the table name. Or change the table name to QuoteLine.

It depends on the application, however I have found great benefit from the first design in several situations. These are typically for reporting\ batch processing applications, rather than OLTP. Essentially where your queries will process large numbers of like rows, rather than singletons. I have promised blindman here (a confirmed sceptic of this having any benefit) a proof of concept but have yet to code it up.

pootle flump ur codings are working excelent.

Actually, I am not totally sure that I am a skeptic.