SSE 2008: A Special Kind of Identity

Giganews Newsgroups
Subject: SSE 2008: A Special Kind of Identity
Posted by:  Gene Wirchenko (gen…@ocis.net)
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?

Sincerely,

Gene Wirchenko

Replies