SSE 2008: A Special Kind of Identity

Giganews Newsgroups
Subject: SSE 2008: A Special Kind of Identity
Posted by:  Gene Wirchenko (gen…
Date: Fri, 15 Apr 2011

Dear SQLers:

    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),
          CommonAttr whoknows
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?


Gene Wirchenko