• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

how to design invoice running number

 
Ranch Hand
Posts: 472
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 1228
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
If you are using Oracle then go for a Sequence object.
 
Nakata kokuyo
Ranch Hand
Posts: 472
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 472
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 98
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 472
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
thanks Chris Hendy and jeanne for the information, i will look though it further , have a nice day
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic