locked
Cyclic Chain of References in Database RRS feed

  • Question

  • Is there a general sense that it is a poor practice to have tables that link up relationally through different paths?

    Example:

    Customer may have many locations

    Customer may have many credit cards

    Customer may have many orders

    Order may be paid by many credit cards.

    Two pathways exist in this example to the table itemizing order payments for a given card:

    Customer -> Card -> Payment

    Customer -> Order -> Payment

    Obviously cascading updates and deletes yield a problem here.  I can work my way around that.  But is the design flawed to begin with?  I do want to create the relational integrety assurances formed by this cycle.  Is there a better way?

     

    Thank you for any help you can lend.


    Jack
    Monday, June 13, 2011 10:17 PM

Answers

  • 1) If by normal you mean do people do this, i don't really know, but i don't think it is done too often.
    2) It is certainly acceptable, as with the proper FKs, the data cannot be bad.
    3) I *think* this is best, but i am quite biased as i came up with this when i needed it once. :)

    Here's the deal. Repeating data is considered a no-no. So, here it "should" be considered bad too. But, i believe the reason repeated data is bad is because:

    a) When there are two sources of the same data they can be in conflict.
    b) It is redundant.

    In this case, it cannot be in conflict due to the FKs, and it isn't redundant, because it forces RI.

    All in all, you need Customer as an FK in both Credit_Card and Order for ownership. The only "extra" COLUMN is Customer in Order_Credit_Card, and there it ties two TABLEs together. I like to call that an "administrative COLUMN", in that the data is not required, but the database needs it for other purposes.


    • Marked as answer by Jack Herr Friday, June 17, 2011 1:34 AM
    Tuesday, June 14, 2011 11:21 PM
    Answerer

All replies

  • Sorry -- the first of the four relationships is irrelevant for this example (though not for the actual design of the related database).  So it is more simply:

    Customer may have many credit cards

    Customer may have many orders

    Order may be paid by many credit cards.

    Two pathways exist in this example to the table itemizing order payments for a given card:

    Customer -> Card -> Payment

    Customer -> Order -> Payment


    Jack
    Monday, June 13, 2011 10:20 PM
  • Put Customer in each TABLE, and use a composite FK in Order_Credit_Card:

    Customer
    --------
    Id    (PK)

    Credit_Card
    -----------
    Number    (PK)
    Customer (FK)
    UNIQUE KEY(Number, Customer)

    Order
    -----
    Id (PK)
    Customer (FK)
    UNIQUE KEY(Id, Customer)

    Order_Credit_Card
    -----------------
    Order (PK)
    Customer
    Credit_Card
    Amount
    FK(Order, Customer) REFERENCES Order(Order, Customer)
    FK(Credit_Card, Customer) REFERENCES Credit_Customer(Credit_Card, Customer)
    Tuesday, June 14, 2011 2:59 AM
    Answerer
  • Thank you, Brian.  This gives me a way to define and link the tables in question.  I think, however, that the relationships still create two hierarchical paths from customer to payment.  I wonder if this is (1) normal, (2) deemed acceptable, and (3) perhaps deemed the best (or only) way to create the relational integrity constraints intended.

    Thanks.


    Jack
    Tuesday, June 14, 2011 6:23 PM
  • 1) If by normal you mean do people do this, i don't really know, but i don't think it is done too often.
    2) It is certainly acceptable, as with the proper FKs, the data cannot be bad.
    3) I *think* this is best, but i am quite biased as i came up with this when i needed it once. :)

    Here's the deal. Repeating data is considered a no-no. So, here it "should" be considered bad too. But, i believe the reason repeated data is bad is because:

    a) When there are two sources of the same data they can be in conflict.
    b) It is redundant.

    In this case, it cannot be in conflict due to the FKs, and it isn't redundant, because it forces RI.

    All in all, you need Customer as an FK in both Credit_Card and Order for ownership. The only "extra" COLUMN is Customer in Order_Credit_Card, and there it ties two TABLEs together. I like to call that an "administrative COLUMN", in that the data is not required, but the database needs it for other purposes.


    • Marked as answer by Jack Herr Friday, June 17, 2011 1:34 AM
    Tuesday, June 14, 2011 11:21 PM
    Answerer
  • Thanks, Brian.  Does anyone else want to weigh in?  Brian's responses, and my predilections, make me think that this is the best approach if I want to ensure relational integrity via the database itself.


    Jack
    Wednesday, June 15, 2011 6:11 PM