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

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.
>
> This means that the payment for Id=1 ($500) in the payments table needs
> to be reduced by $100 (ref=2) and $50 (ref=6) , similarly, the payment
> for Id=3 ($1000) needs to be reduced by $850). So the question is:
>
> How do I design the adjustments table to ensure that:
> a) For any value entered in the Id column a check should occur to
> ensure that it does not exist in the Ref column.
> b) For any value entered in the Ref column, a check should occur to
> ensure that it does not exist in the Id column.
>
> In other words, looking at the adjustments table, I should be
> prevented from entering 2,4,6,5 in the Id column and 1, 3 in the Ref
> column.

I th8ink you can put a trigger on the table that can check the
constraints.

http://www.postgresql.org/docs/8.2/static/triggers.html

However, I wonder whether it might not make more sense to go with an
account system, with an account balance and credits and debits to the
account. Is the system you are proposing really the best data model?

Sean