none
Schedule payment design suggestions

    質問

  • I appreciate if I can get some help with some schema design. 

    I have Payee table

    tbl_Payee (Individual)
    Payee ID,Payee Name
    P1,Payeetest

    tbl_Payee_customer
    ID,Payee ID,CustomerID
    PC1,P1,C1

    I have insurance table (It

    tbl_Insurance (Insurance)
    Insurance ID,Insurance Name
    I1,Instest

    tbl_Insurance_customer
    Insurance ID,CustomerID
    IC1,I1,C1

    I have schedule Payments table were it has all the Payment schedule information
    (e.g)

    tbl_Schedule_payment

    ScheduleID,Startdt,EndDt,PaymentAmt,Freq

    Sch1,01/01/2012,12/31/2012,$50.00,Monthly


    My question is how I can design tbl_Schedule_payment to be used by both Individual Payee and Insurance meaning we should be able to setup payment schedule to both Individual Payee and Insurance for a single customer

    one way I was thinking was to create another table

    tbl_Customer_Schedule_payment

    ID,TypeofPayee,Value,scheduleID
    1,Insurance,IC1,Sch1
    1,Payee(Individual),PC1,Sch1

    Is this a good design or any suggestions appreciate.

    Thanks
    Kris


     


    Kris


    • 編集済み Kris_b 2012年3月1日 23:17
    2012年3月1日 23:08

回答

  • That design cannot include a FOREIGN KEY. To allow an FK in the same design:

    tbl_Customer_Schedule_payment

    ID,Payee ID(FK), Insurance ID(FK),scheduleID
    CONSTRAINT CHECK((Payee ID IS NULL AND Insurance ID IS NOT NULL) OR (Payee ID IS NOT NULL AND Insurance ID IS NULL))
    1,NULL,IC1,Sch1
    1,PC1,NULL,Sch1

    It might be better to a associate each object in its own TABLE. That is, Insurance_Payeee and Customer_Payee.


    2012年3月2日 13:10
    回答者:

すべての返信

  • Hi Kris,

    Yes, you can create a "customer schedule payment" as you explained. Below is other option you can try with,

    This helps to avoid an additional table. customer insurance id will be null for Insurance and vice versa. Please let us know if you have any questions.

    ScheduleID
    Startdt
    EndDt
    PaymentAmt
    Freq
    custInsuranceId

    insInsuranceId


    Thanks,
    Suresh
    Please use Mark or Propose as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    2012年3月2日 5:53
  • That design cannot include a FOREIGN KEY. To allow an FK in the same design:

    tbl_Customer_Schedule_payment

    ID,Payee ID(FK), Insurance ID(FK),scheduleID
    CONSTRAINT CHECK((Payee ID IS NULL AND Insurance ID IS NOT NULL) OR (Payee ID IS NOT NULL AND Insurance ID IS NULL))
    1,NULL,IC1,Sch1
    1,PC1,NULL,Sch1

    It might be better to a associate each object in its own TABLE. That is, Insurance_Payeee and Customer_Payee.


    2012年3月2日 13:10
    回答者:
  • Thanks guys!

    I know I have to compromise on the Foreign key constraints.  This payment schedule may be used for other parties aswell.

    Kris


    Kris

    2012年3月2日 23:00
  • PLEASE, if possible remove the tbl_ prefix in your naming convention

    John

    http://knowledgy.org

    2012年3月5日 21:48
  • PLEASE, if possible remove the tbl_ prefix in your naming convention

    John

    http://knowledgy.org
    Though redundant, many people like it. Suggest it, convert them to our cause, but be nice about it. :)
    2012年3月6日 12:51
    回答者:
  • I wasn't trying to be mean and I apologize if i sounded that way.

    John

    http://knowledgy.org

    2012年3月6日 22:38
  • Not mean. But a capitalized "PLEASE", and a suggestion tom remove a prefix with no reason given.. well, i prefer to say it otherwise:

    Either the cold statement: The tbl_ prefix is redundant.

    Or an "I statement": I prefer leaving off the tbl_ prefix, as it is redundant.

    2012年3月12日 12:50
    回答者:
  • Would you be open to using a surrogate key Uniqueidentifier for PayeeID and Insurance ID and use it for left outer joins on a intermediate table which maps the the schedule table. Since the GUID is unique your assured of the rigth mapping without having to add a column to identify the type of relationship and you would also have the tables loosely coupled to make any future extensions as well.

    2012年3月13日 9:10