Re: [NOVICE] Recursive relationship - preventing cross-index entries.

[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