jeudi 1 mars 2012 23:08
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.
- Modifié Kris_b jeudi 1 mars 2012 23:17
Toutes les réponses
vendredi 2 mars 2012 05:53
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.
- Proposé comme réponse Ed Price - MSFTMicrosoft Employee, Owner jeudi 6 décembre 2012 00:52
vendredi 2 mars 2012 13:10Auteur de réponse
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.
- Modifié Brian TkatchMicrosoft Community Contributor, Editor vendredi 2 mars 2012 13:12
- Marqué comme réponse Ed Price - MSFTMicrosoft Employee, Owner jeudi 6 décembre 2012 00:52
vendredi 2 mars 2012 23:00
I know I have to compromise on the Foreign key constraints. This payment schedule may be used for other parties aswell.
lundi 5 mars 2012 21:48PLEASE, if possible remove the tbl_ prefix in your naming convention
mardi 6 mars 2012 12:51Auteur de réponse
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. :)
mardi 6 mars 2012 22:38I wasn't trying to be mean and I apologize if i sounded that way.
lundi 12 mars 2012 12:50Auteur de réponse
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.
mardi 13 mars 2012 09:10
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.