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.

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 (DOT) a.chance.ie> wrote

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 (DOT) a.chance.ie> wrote

a) what is wrong with the first table structure?

He has split an attribute, so he has two columns, one of which is
always zero. Attribute splitting is taking an attribute and making
its values into either tables or attributes on their own. Example:

1) Split a temporal value into tables, as if you were using mag tapes
again:

CREATE TABLE Jan_2003 (...); CREATE TABLE Feb_2003 (...);

2) Split a code into columns:
CREATE TABLE Foobar
(..
male CHAR(1) NOT NULL CHECK (male IN ('y','n'),
female CHAR(1) NOT NULL CHECK (female IN ('y','n'),
..);

b) if (and how) the second one is better)?

It is in a proper domain key normal form (DKNF); he needs a constraint
to enforce his model.

c) how to get the result wanted from the second table structure?

SELECT ...
SUM(CASE WHEN posting = 'CD' THEM amt ELSE 0.00 END) AS tot_cd,
SUM(CASE WHEN posting = 'DB' THEM amt ELSE 0.00 END) AS tot_db,
SUM(CASE WHEN posting = 'CD' THEM amt ELSE -amt END) AS
tot_balance,
FROM ...
WHERE ...;

joe.celko (AT) northface (DOT) edu says...


Mr Celko,

Thank you for your reply. Just a couple of clarifications if I may?

1) I presume that by "constraint to enforce his model" you mean a
primary key of some sort - i.e. there should be a Transaction_ID field
(integer NOT NULL)?


2) The table structure proposed by me was like this:

-----------------------------------------------
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
-----------------------------------------------


Is there not a problem in the fact that the column Tr_Month contains
data which is already present in the column Tr_Date, and therefore that
introduces a redundancy and hence the table is not properly normalised?


I know from my travels on the internet that you have given courses in
how to denormalise tables - is this a "real world" situation where the
purist's theory is justifiably put aside in favour of
performance/simplification criteria?



Unfortunately, Interbase (so far) is an implementation which doesn't
support the CASE construct (though it really, *_really_* should).

Thanks for your input so far.


Paul...

--

plinehan__AT__yahoo__DOT__com

C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro

Please do not top-post.

On Tue, 21 Oct 2003 13:39:21 +0100, Paul wrote:



[snip]

As to your question about Transaction Date and Transaction Month. The
former is the actual calendar date. The latter is the accounting month in
which that date falls. This is valid since many businesses deviate from
the calendar in order to have data which is comparable from year to year.
(Two popular schemes are dividing the year into 4 quarters each of which
has 2 months of 4 weeks and 1 of 5 weeks and the use of a calendar with 13
4-week months.)

A more common situation is the use of adjusting, closing, and reversing
entries made when the books are closed at the end of a period. In
general due to time lag the transactions are entered some time after the
last day of the period, but apply to the period.

[sic](integer NOT NULL)? <<

Columns are not fields; rows are not records and tables are not files.
But yes, a transaction identifier is probably part of the process and
you want to record it. And a lot more constraints than just that are
needed.

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. And I hop you know that Standard SQL uses the
ISO-8601 date format instead of what you wrote.

Try something like this:

CREATE TABLE Transactions
(trans_id INTEGER NOT NULL PRIMARY KEY,
acct_nbr DECIMAL (9) NOT NULL
REFERENCES Accounts (acct_nbr)
ON UPDATE CASCADE,
trans_date DATE DEFAULT CURRENT_DATE NOT NULL
trans_type CHAR(2) NOT NULL
CHECK(trans_type IN ('CR', 'DB')),
amount DECIMAL(12,2) NOT NULL
CHECK (amount >= 0.00));

therefore that introduces a redundancy and hence the table is not
properly normalised? <<

It is not normalized and you need another table in the schema; a
Calendar table keyed on the calendar date, with all the temporal
information the enterprises requires (fiscal years, holidays, etc.).
Do a JOIN to the Calendar to get the reporting week, month, quarter or
year.

the purist's theory is justifiably put aside in favour of
performance/simplification criteria? <<

I mention denormalization when I was doing SQL classes and put in the
same category as an emergency tracheotomy -- it is not the first thing
you do when someone coughs. The Calendar table will work fine for
your situation and it will be very, very useful in the system as a
whole.