Re: Foreign key

From: --CELKO-- <71062.1056_at_compuserve.com>
Date: 3 Feb 2003 13:59:58 -0800
Message-ID: <c0d87ec0.0302031359.3abba8b1_at_posting.google.com>

You nmight want to look up matrix methods in accounting. Imagine a big square matrix with all of your accounts on each axis. The rows in the grid are debits, the columns are credits. To show movement from one account to another, you enter an amount in a cell in the grid. Each transaction has a unique identifier and might put various amounts in many cells at once.

This is modeled in SQL as:

CREATE TABLE Transactions
(trans_id INTEGER NOT NULL,
 debit_acct DECIMAL (8,3) NOT NULL

            REFERENCES ChartOfAccounts(acct_nbr)
            ON UPDATE CASCADE,
 credit_acct DECIMAL (8,3) NOT NULL 
            REFERENCES ChartOfAccounts(acct_nbr)
            ON UPDATE CASCADE,

 CHECK (debit_acct <> credit_acct),
 amount DECIMAL (12,4) NOT NULL

        CHECK (amount >= 0.0000),
 PRIMARY KEY (trans_id, debit_acct, credit_acct));

All of your specialized ledgers are VIEWs taken from this table. They are restrict to certain account codes from your Chart of Accounts, which I assume is a heirarchy. Received on Mon Feb 03 2003 - 22:59:58 CET