Schedule payment design suggestions

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

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

Answer 4

PLEASE, if possible remove the tbl_ prefix in your naming convention

---

John

http://knowledgy.org

Answer 5

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. :)

Answer 6

I wasn't trying to be mean and I apologize if i sounded that way.

---

John

http://knowledgy.org

Answer 7

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.

Answer 8

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.