[Removing pgsql-novice. Please don't cross-post. Choose one list or
another at a time. ]
On Jun 19, 2007, at 23:04 , Andrew Maclean wrote:
I got no answer so I am trying again.
In a nutshell, if I have a recrusive relationship as outlined
below, how do I implement a rule for the adjustments table that
prevents the entry of an Id into the Ref column if the id exists in
the Id column and vice versa?
If I have a payments table which holds an Id and a payment and I
also have an adjustments table that holds a payment id and a
reference id so that adjustments can be made to payments.
So the payments table looks like this:
Id Payment
1 500.0
2 100.0
3 1000.0
4 50.0
5 750.0
6 50.0
7 800.0
8 1200.0
and the adjustments table looks like this:
Id Ref
1 2
3 4
1 6
3 5
The idea is that, if for example Id=1 is a credit dard payment,
then entries 2 and 6 could be payments that are already included in
the credit card payment so we need to adjust the total payment to
take this into account.
I guess I don't really understand why your schema is set up this way.
It seems like the amounts for 2, 4, 5, and 6 are of a different type
than those of 1 and 3, so I'd put them in two different tables. It
seems that 2, 4, 5, and 6 are more like amounts due, while 1 and 3
are payments made against those due amounts. This
CREATE TABLE accounts_receivable
(
accounts_receivable_id INTEGER PRIMARY KEY
, amount NUMERIC NOT NULL
);
CREATE TABLE receipts
(
receipt_id INTEGER PRIMARY KEY
, amount NUMERIC NOT NULL
);
CREATE TABLE accounts_receivable_receipts
(
accounts_receivable_id INTEGER NOT NULL
REFERENCES accounts_receivable
, receipt_id INTEGER NOT NULL
REFERENCES receipts
, PRIMARY KEY (accounts_receivable_id, receipt_id)
);
So, using the numbers you have above, you'd have
INSERT INTO accounts_receivable (accounts_receivable_id, amount)
VALUES (2, 100.0), (4, 50.0), (5, 750.0), (6, 50.0);
INSERT INTO receipts (receipt_id, amount)
VALUES (1, 500.0), (3, 1000.0);
INSERT INTO accounts_receivable_receipts (accounts_receivable_id,
receipt_id)
VALUES (2, 1), (4, 3), (6, 1), (5, 3);
I have not done much accounting-style design, and I don't think this
is really the best way to set these up (for example, I think it's a
bit odd to map these amounts against each other without indicating
how much of the amount is matched), but without more information
about your business logic, I don't really know what else to suggest.
Hope this helps.
Michael Glaesemann
grzm seespotcode net
