The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Database table design and concurrency?

I am developing a software for an organization where they have Receipt Books numbered from 1 to n. Each Receipt Book has receipts numbered from 1 to 100.

In my database, other tables have the ID field which is incremented by a sequence. But for this (Receipts) table, there is no ID field.

The table structure is somewhat like this:

ReceiptNo    number(5)
BookNo      number(3)
FeetypeID    number(2)
Amount      number(9,2)
PaymentDate  date

Primary Key (ReceiptNo,BookNo,FeeTypeID)

Receipt No. will be incremented by finding the max value from the existing Receipt Nos. corresponding to that Book No. and FeeTypeID.

This case has a drawback as compared to the sequences in other tables. Sequences are automatically handled by the database if two users simultaneously enter data. But for the Receipts table where there is no need to define a sequence, one user will find the Max(ReceiptNo) and type in the rest of the entries. So there is a chance that in the meantime another user on a different machine will also get the same max(ReceiptNo) until the record of the previous user gets saved.

So how to solve this problem?
Rohit. Send private email
Monday, February 26, 2007
You have the same problem whether you use a sequence for the primary key or not, because you still need to generate the (ReceiptNo,BookNo,FeeTypeID) tuple and ensure that it's unique. The approach I'd take is to create the receipt book as soon as th user begins data entry.
Monday, February 26, 2007
Some possibilities:

Each user updates a separate book.
Each user indicates how many receipts they need when they start data entry.
Each user has to save each receipt separately.
Rich Fuchs
Monday, February 26, 2007
So. You want autonumbers that aren't autonumbers. Or sequences. You might want to take a look at how Oracle folks handle these.

Here are a couple random links:

How are you going to handle abandoned transactions? Where a user starts entering a receipt, another user starts a second later to enter a new receipt, the first user cancels (pressed cancel, computer crashed, some idiot with a backhoe destroys internet connectivity in your state, etc) while the second one completes the transation.

My opinion/recommendation is that they get a ReceiptNo only when the save is successful and committed.

Or maybe I'm misunderstanding your question, and ReceiptNo ranges from 1-100. In which case, when you create a new ReceiptBook, you should also create the 100 receipts for that book. If you go purchase receipt books at an office supply store, they all have large numbers that are most likely going to be unique. That prevents crooked cashiers from having two books with the same number. Or waiters from recycling receipts. If this is the case, you don't want different users to use the same receipt book.
Monday, February 26, 2007
Primary Key (ReceiptNo,BookNo,FeeTypeID)

Never do this. Just have a single id that the db handles.
mynameishere Send private email
Monday, February 26, 2007
Peter is absolutely right…

The user must enter the Receipt number… You can use the select max() as default receipt number but let the user override. What if the user rips ups a receipt and writes a new one? What if the pages are not used in order?

In this situation the Receipt number must be controlled by the paper not the revise.
Verizon Sucks!
Monday, February 26, 2007
If I understood your problem corectly, you are trying to prevent the race condition where 2 users might get the same receipt number by running the sp at the same time.

If you are using MS SQL take a look at 'sp_getapplock' ( ). It works like a normal semaphore.

Dunno what other RDBs use for locks, but I assume most of them would have something of an equivalent.
Radu094 Send private email
Tuesday, February 27, 2007
"The user must enter the Receipt number… "

Agreed. How can you automatically determine a receipt number if it is something that is printed on some physical piece of paper? The user should enter it and you should check for duplicates in the database.

And you should consider steering them away from paper receipts if possible. It's time for them to get a real POS system. I'd be happy to sell you one...  ;)
dood mcdoogle
Tuesday, February 27, 2007
"Dunno what other RDBs use for locks, but I assume most of them would have something of an equivalent. "

In Oracle, DBMS_LOCK
David Aldridge Send private email
Sunday, March 11, 2007

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz