|Subject:||SSE 2008: A Special Kind of Identity|
|Posted by:||Gene Wirchenko (gen…@ocis.net)|
|Date:||Fri, 15 Apr 2011|
I continue slowly putting together a Banking database. I now
want to define the Transactions table. It makes sense to keep
transactions together somehow. For example, a transfer from one
account to another is composed of a transfer out and a transfer in. It
would be good to be able to see all parts of such a batch.
I suppose that transactions could be corrected after the fact, so
I am really only concerned with a batch balancing when it is entered.
How do I generate the batch number? (I do not want the application
doing this. I want it done in a stored procedure.)
If I have a common attribute for each transaction in a batch, I
could do something like:
create table TranBatches
BatchNr int identity(1,1),
This common attribute might be transaction date. The transaction
table would have an FK into TranBatches.
What if there is no common attribute? Then, all I would have is
for TranBatches is a table of numbers. This strikes me as silly.
I suppose that I could roll my own with a table containing the
last batch number generated and add one to it in the stored procedure
I come up with for batch processing, but this strikes me as kludgy.
Is there an elegant way to generate batch numbers for the
transactions table, bearing in mind that a batch number will be used
for more than one row in the transactions table?