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

From: Sean Davis <sdavis2(at)mail(dot)nih(dot)gov>
To: a(dot)maclean(at)cas(dot)edu(dot)au
Cc: pgsql-novice(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org
Subject: Re: [NOVICE] Recursive relationship - preventing cross-index entries.
Date: 2007-06-20 10:49:41
Message-ID: 46790645.4040108@mail.nih.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-novice

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David Abrahams 2007-06-20 10:55:06 Need help with generic query
Previous Message Marco Colombo 2007-06-20 10:33:28 Re: unexpected shutdown

Browse pgsql-novice by date

  From Date Subject
Next Message Carol Cheung 2007-06-20 13:20:44 meaning of "create role"
Previous Message Richard Huxton 2007-06-20 07:49:18 Re: [NOVICE] Recursive relationship - preventing cross-index entries.