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 ?
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 ]
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.
Joined: Apr 13, 2005
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?