I appreciate if I can get some help with some schema design.
I have Payee table
Payee ID,Payee Name
I have insurance table (It
Insurance ID,Insurance Name
I have schedule Payments table were it has all the Payment schedule information
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
Is this a good design or any suggestions appreciate.
- 編集済み Kris_b 2012年3月1日 23:17
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
Please use Mark or Propose as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
- 回答の候補に設定 Ed Price - MSFTMicrosoft Employee, Owner 2012年12月6日 0:52
That design cannot include a FOREIGN KEY. To allow an FK in the same design:
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))
It might be better to a associate each object in its own TABLE. That is, Insurance_Payeee and Customer_Payee.
- 編集済み Brian TkatchMicrosoft Community Contributor, Editor 2012年3月2日 13:12
- 回答としてマーク Ed Price - MSFTMicrosoft Employee, Owner 2012年12月6日 0:52
I know I have to compromise on the Foreign key constraints. This payment schedule may be used for other parties aswell.
2012年3月5日 21:48PLEASE, if possible remove the tbl_ prefix in your naming convention
PLEASE, if possible remove the tbl_ prefix in your naming conventionThough redundant, many people like it. Suggest it, convert them to our cause, but be nice about it. :)
2012年3月6日 22:38I wasn't trying to be mean and I apologize if i sounded that way.
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.
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.