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

Paul,

In most accounting systems, a debit to one account is a credit to another. In both of your tables, this means the insertion of two rows for one transaction, and a greater risk of data corruption which you have to manage. Better would be:

CREATE TABLE account_history (
 trans_no INT NOT NULL PRIMARY KEY,
 trans_dt TIMESTAMP NOT NULL,
 dr_acct_no INT NOT NULL REFERENCES accounts (acct_no),  cr_acct_no INT NOT NULL REFERENCES accounts (acct_no),  trans_amt DECIMAL(12,4) NOT NULL,
 CHECK (dr_acct_no <> cr_acct_no));

... and creating a report like the one you describe is simple:

SELECT
  a.acct_no,
  a.acct_name,
  SUM(dr.trans_amt) AS debits,
  SUM(cr.trans_amt) AS credits
FROM
  accounts AS a JOIN
  account_history AS dr ON a.acct_no = dr.dr_acct_no JOIN   account_history AS cr ON a.acct_no = cr.cr_acct_no GROUP BY
  a.acct_no,
  a.acct_name;

"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:45:20 CEST