Transactions than can be rejected, temporary tables or not? [message #673777] |
Mon, 10 December 2018 04:42 |
|
OraFerro
Messages: 433 Registered: July 2011
|
Senior Member |
|
|
Hi All,
I need consultation regarding a design issue. I have a transaction (creation of voucher, journal entry, and sub-ledger), data are entered and they are prone to approval/rejection. Since data affects more than one table, I though of the following options:
1- Create a temporary structure of tables (temp_voucher, temp_journal, temp_sublegdger)
- This way tables that contain approved data will not be affected unless transaction is approved.
- No disruption to VOUCHER_NUMBER that is automatically created when new record has been entered and its sequential nature is important to business.
- Log data is available in case there is a need to refer to rejected transactions.
However
- Extra tables are created.
- Copy approved transaction on the approved-data tables is not straight forward as there are business rules that should be checked.
2- Use the same structure but with different key ranges or add new indicator field (is_temporary)
- No extra tables created
- Same insert business rules
- Possibility to consider temporary data (inserted before approval) in new business rules (from one table)
- Simple actions in case of approval or rejection
However
- Reports and other PLSQL queries should be adjusted to exclude temporary data
- Create entries with fake VOUCHER_NUMBER and then adjust it in case of approval across all tables (referential integrity)
3-Other solutions that I am not aware of.
Thanks,
Ferro
[Updated on: Mon, 10 December 2018 04:46] Report message to a moderator
|
|
|
|
|
|
Re: Transactions than can be rejected, temporary tables or not? [message #673816 is a reply to message #673814] |
Tue, 11 December 2018 01:23 |
|
OraFerro
Messages: 433 Registered: July 2011
|
Senior Member |
|
|
Thanks Michel for your detailed reply, appreciated really...
Quote:
You point yourself to the flaw
I agree with you, it is not something holy but I meant it is not the same flaw as if I am trying to make sure that the sequence-generated primary key is gap-less. Its just a business preference and its not recommended to take a direction that would make this field ALWAYS not in sequence.
Quote:
Why not just a STATUS column in your table?
So your recommendation is option 2 in my choices
Quote:Use the same structure ....add new indicator field (is_temporary) to act as a status?
Thanks,
Ferro
[Updated on: Tue, 11 December 2018 01:24] Report message to a moderator
|
|
|
|
|
Re: Transactions than can be rejected, temporary tables or not? [message #674037 is a reply to message #673818] |
Fri, 28 December 2018 13:51 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
The only thing I can think of is to store the voucher number as a negative number (negative sequence?) when it is approved then do the following
1) lock the table exclusive.
2) select max(voucher_number) + 1
3) update the negative voucher number in all files to the value found in 2.
4) commit, which releases the table lock.
Since you are ONLY locking the table when you are approving the voucher the lock should be very quick and the indicator that it needs to be approved/cancelled is that it has a negative voucher number. If it is cancelled then simply delete all rows with the negative voucher value.
[Updated on: Fri, 28 December 2018 13:52] Report message to a moderator
|
|
|
|