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

From: Paul <paul_at_not.a.chance.ie>
Date: Mon, 20 Oct 2003 18:47:12 +0100
Message-ID: <MPG.19fe2fafa341abc79897a1@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:

what is wrong with the first table structure?
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 - 12:47:12 CDT