a problem with one to many relationships

[ngoz1]

please dont get too hung up on my example it was just an example i created off the top of my head to expalin my question.
all i am really trying to find out is "what is right thing to do in a one to many relationships were the many side is optional."

let me give you a real life example from the database i am creating for BWA :-
entities: member and payment
a member may make one or many payments(optional)(usually a registration payments which is renewed every year)

but a particular payment is made by one member.

1)am i right in the way i am relating the two entities and

2)if so,seeing that the many side is optional, do i just post the member primary key into the payment table or do i create a third table between member and payment and post the member id and payment id into this table (just like i would do if this was a many to many relationship)

3)if a third table is created, which of the keys is meant to be the primaty key - member id or payment id?


cheers and thanks for your time
ng

 

[LittleSmudge]

Okay so lets put this in table form

tblMember
MemberId ( Prime Key )
MemberName
MemberInsideLegMeasure
etc.. ..
etc..


tblPayment
PaymentUniqueId ( Prime Key )
MemberRef ( Foreign Key ) To link to which member made the payment
PaymentDate
PaymentAmount
PaymentCurrency
etc.. ..
etc..

So in your database you have lots of members.
A member can make one, some, or fewer payments.

SO You also have lots and lots of payments.

- but for any given payment you know who made the payment because the payment has the member's ID stored with it.

If member "Mr Scrimpy NoCash" has never made a payment ( EVER ) then
There is no PaymentDate to store for his non existend payment
There is no PaymentAmount to store for his non existend payment
There is no PaymentCurrency to store for his non existend payment
.. .. the point is THERE IS NO RECORD

So don't get hung up about the MemberRef field being blank because the link is "optional".
No payment = No record = No Link = No problem


'ope-that-'elps

G LS