Re: This table strikes me as wrong - could someone explain why?

There's really nothing wrong with either one- they are just different, and it really just depends on what the business rules are. Apparently, one business transaction can consist of a debit and a credit, so that part is correct. Storing the net amount just makes it easier for reports, though I am certain there will be an argument over this point (many feel that aggregated information should never be stored - at least not in an OLTP system).

Your method would also work, but (assuming the business rule I inferred is correct) you would need to store a transaction_id (which would NOT be a PK by itself, BTW) to be able to recreate all parts (debit and credit) of a single transaction.

"Paul" <paul_at_not.a.chance.ie> wrote in message news:MPG.19fe2fafa341abc79897a1_at_news1.eircom.net...
>
>
> Hi all,
>
>
> I was shown a table (AccountHistory) structure today which looked like
> this.
>
>
> Ac_ID Transaction_Date CR DB
> 1 02/01/2003 100 0
> 1 03/01/2003 320 0
> 2 03/01/2003 500 0
> 2 04/02/2003 0 250
>
>
> CR and DB are Credit and Debit. I didn't like this idea, and thought
> that the table would be better like this (probably with a Tr_ID primary
> key as well).
>
>
> Ac_ID Tr_Date Tr_Month Tr_Type Amount
> 1 02/01/2003 1 CR 100
> 1 03/01/2003 1 CR 320
> 2 03/01/2003 1 CR 500
> 2 04/02/2003 2 DB 250
>
>
> However, the person who uses the first structure says it facilitates his
> reports, and certainly, on first glance it seems to me that this is true
> - he wants reports in the form
>
> Title
> ===================
> Account ID: XXXXXXXXX
> Account Name XXXXXXXXX
> ==================
> /// Report rows
> Month Total DB Total CR NetBalance
> 1 1000 500 500 DB
> 2 3000 1500 1500 DB
> 3 1000 600 500 DB
> ...etc.
>
>
> Could anyone explain to me:
>
> a) what is wrong with the first table structure?
>
> b) if (and how) the second one is better)?
>
> and finally,
>
> c) how to get the result wanted from the second table structure?
>
>
>
> Paul...
>
>
> p.s. I have noticed in the past that some questions similar to this get
> a "tell your professor to fail you" - this is not an assignment - I'm
> actully trying to help somebody else out on the
> borland.public.interbase.sql newsgroup - see the recent "Complex SQL
> statment question" thread, but am not 100% sure of my theorectical
> ground, and I would appreciate explanations, references, URL's, debates,
> discussions, ideas, rants and raves from knowledgeable people who are
> interested in helping people out on this group.
>
>
> --
>
> plinehan__AT__yahoo__DOT__com
>
> C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro
>
> Please do not top-post.
Received on Mon Oct 20 2003 - 22:01:15 CEST