From: | Michael Glaesemann <grzm(at)seespotcode(dot)net> |
---|---|
To: | a(dot)maclean(at)cas(dot)edu(dot)au |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: [NOVICE] Recursive relationship - preventing cross-index entries. |
Date: | 2007-06-20 04:40:30 |
Message-ID: | B1314E34-7955-4B1C-AB0B-62D82C9366CC@seespotcode.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-novice |
[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
From | Date | Subject | |
---|---|---|---|
Next Message | nasim.sindri | 2007-06-20 06:25:53 | commit transaction failed |
Previous Message | Andrew Maclean | 2007-06-20 04:04:05 | Recursive relationship - preventing cross-index entries. |
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2007-06-20 07:49:18 | Re: [NOVICE] Recursive relationship - preventing cross-index entries. |
Previous Message | Andrew Maclean | 2007-06-20 04:04:05 | Recursive relationship - preventing cross-index entries. |