Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004
    Posts
    115

    Design Issue Fan Trap

    Hi

    Consider a financials system with tables account, payment, and receipt.
    An account can have many payments and many receipts.
    Any query that tries to select payments and receipts together will obtain a cross product of the payments and receipts for each account.

    For example:


    Code:
    SELECT a.account_no, sum(payment_amt), sum(receipt_amt)
    FROM   account a, payment p, receipt r
    WHERE  a.account_no = p.account_no
    AND    a.account_no = r.account_no
    GROUP BY a.account_no;
    One way to get around this is

    Code:
    SELECT a.account_no, p.payment_total, r.receipt_total
    FROM   account a
    ,(     SELECT account_no, sum(receipt_amt) as receipt_total
           FROM   receipt
           GROUP BY account_no) r
    ,(     SELECT account_no, sum(payment_amt) as payment_total
           FROM   payment
           GROUP BY account_no) p
    WHERE  a.account_no = r.account_no
    AND    a.account_no = p.account_no
    GROUP BY a.account_no
    I am sure the relationship i am exposing is an example of a fan trap.
    But how do i resolve them in my case ?

    The way the system is documented makes me think like an account can have multiple receipts and multiple payments .
    But how do i rearrange with the correct wordings to make sure i break the fan trap.

    regards
    db2hrishy

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you were ~so~ close with your query, which was correct except for the outer GROUP BY, which is not required

    using JOIN syntax...
    Code:
    SELECT a.account_no
         , p.payment_total
         , r.receipt_total
      FROM account a
    INNER
      JOIN (
           SELECT account_no
                , sum(receipt_amt) as receipt_total
             FROM receipt
           GROUP BY account_no
           ) r
        ON r.account_no = a.account_no
    INNER
      JOIN (  
           SELECT account_no
                , sum(payment_amt) as payment_total
             FROM payment
           GROUP BY account_no
           ) p
        ON p.account_no = a.account_no
    using correlated subqueries...
    Code:
    SELECT a.account_no
         , (
           SELECT sum(receipt_amt) 
             FROM receipt
            WHERE account_no = a.account_no
           ) as receipt_total  
         , (  
           SELECT sum(payment_amt) 
             FROM payment
            WHERE account_no = a.account_no
           ) as payment_total
      FROM account a
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2004
    Posts
    115
    Hi Rudy

    Thanks for the response.

    I was trying to circumvent the ER shortcomming by elimnating the FAN trap rather then writing the SQL in a esoteric manner.

    I would rather want a simple straight forward sql like the first sql get me the correct results.

    A fan trap can be essentailly resolved by regarranging the tables like this but i am having trouble in english language rearranging the relationship as susggested by the fan trap resolution technique here.

    http://db.grussell.org/section005.html#_Toc67114416

    regards
    Hrishy

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that particular technique will not resolve your fan trap, unless you can associate every payment with a specific receipt (or vice versa)

    in other words, it's quite likely that there is no ER shortcoming
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •