This week's book giveaway is in the OCPJP forum.
We're giving away four copies of OCA/OCP Java SE 7 Programmer I & II Study Guide and have Kathy Sierra & Bert Bates on-line!
See this thread for details.
The moose likes JDBC and the fly likes how to design invoice running number Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "how to design invoice running number" Watch "how to design invoice running number" New topic
Author

how to design invoice running number

Nakata kokuyo
Ranch Hand

Joined: Apr 13, 2005
Posts: 442
hi good day,

can someone suggest some ideas howto design a invoice running number table ?

what i means for the invoice running number is when we create an invoice, the number represent the invoice, so that we can search the invoice by the running number , in fact it act like invoice id

after new invoice created, the running number will be increase 1, the concern is there may have few pc running the same application, if one of the pc have created invoice, the running number changed but other pc still keep the old running number, how we cater this problem ?

how many tables should involve in such scenario ?

thank you very much for guidance
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30789
    
157

Nakata,
There are many different ways to do so. Why don't you share what you have so far and we can give you feedback on it.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Srinivasa Raghavan
Ranch Hand

Joined: Sep 28, 2004
Posts: 1228
If you are using Oracle then go for a Sequence object.


Thanks & regards, Srini
MCP, SCJP-1.4, NCFM (Financial Markets), Oracle 9i - SQL ( 1Z0-007 ), ITIL Certified
Nakata kokuyo
Ranch Hand

Joined: Apr 13, 2005
Posts: 442
hi all , i'm not sure am i doing the right thing ,

i have one table , named tbl_invoice_number and contains
invoice_id column as integer

another table tbl_temp_invoice_number contain temp_invoice_id

scenario 1
-----------
application will check table tbl_temp_invoice_number first,
- if temp_invoice_id is not null, tbl_invoice_number.invoice_id will copy value into temp_invoice_id
- else it take tbl_temp_invoice_number.temp_invoice_id as running number

scenario 2
----------
if other application check in and query,
- if tbl_temp_invoice_number.temp_invoice_id is not null, then return an increment id of tbl_temp_invoice_number.temp_invoice_id as running number

-else tbl_invoice_number.invoice_id will copy value into temp_invoice_id
and take it as running number

scenario 3
-----------
when one of the application save the invoice, tbl_temp_invoice_number.temp_invoice_id value will be clear

-- but the doubt is how application know should increase tbl_temp_invoice_number.temp_invoice_id or just take the tbl_temp_invoice_number.temp_invoice_id ?

believe still have many scenario have not cater yet ...really confuse on it
[ November 17, 2006: Message edited by: Nakata kokuyo ]
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30789
    
157

Nakata,
You are on the right track. It sounds like you are trying to simulate a sequence.

When would temp_invoice_id be null? If you initialize it to 0 or 1, you can avoid this case.

but the doubt is how application know should increase tbl_temp_invoice_number.temp_invoice_id or just take the tbl_temp_invoice_number.temp_invoice_id ?

You never want to just take it. This field represents the either the next available # or the maximum # in use. Either way, it needs to be incremented every time you take a number to be consistent. This should be done in a transaction if this is a real application and not a project.
Nakata kokuyo
Ranch Hand

Joined: Apr 13, 2005
Posts: 442
Jeanne ,

i just summarise what i get form your points here
-------------------------------------------------

table tbl_invoice_number will keep the current running number
(ie: 10001)

table tbl_temp_invoice_number always keep the increment running number
(ie : 10002)

application always take the number from tbl_temp_invoice_number(ie:10002)

when application save invoice, "10002" will update table tbl_invoice_number
then update to table tbl_temp_invoice_number "10002" to "10003"

- means table tbl_temp_invoice_number always increase number , but tbl_invoice_number always update only


do you means this ? or perhaps i have other scenario not cater yet ? any better idea or solutions for this invoice running number case?

thank you very much for your guidance
Chris Hendy
Ranch Hand

Joined: Mar 04, 2006
Posts: 98
This has been discussed in depth from an Oracle perspective on AskTom

http://asktom.oracle.com/pls/ask/f?p=4950:8:15086173034110266238::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:500421805606

http://asktom.oracle.com/pls/ask/f?p=4950:8:15086173034110266238::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:3225867788098

As long as you don't mind gaps go for an Oracle sequence.

If you are not on Oracle, and if your RDBMS provides one, go for an auto-increment column.

Anything else is fraught with risks either of scalabilty or duplication.
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30789
    
157

Nakata,
I mean that you only need one table/column with this value. Having two introduces extra complexity.

That said, Chris is correct that there is no need to write this by hand unless it is a school project.
Nakata kokuyo
Ranch Hand

Joined: Apr 13, 2005
Posts: 442
thanks Chris Hendy and jeanne for the information, i will look though it further , have a nice day
 
 
subject: how to design invoice running number