I have been trying to solve this problem but in every solution there seems to be something missing. So we have organization for collecting donations from donors for people that need help.
Member can create many action for collecting donations and people in need can have many actions for them. So we have following entities :
Now the key thing is that donor can donate only once for a particular action. That means I cant have action table with 3 (pk/fk) keys for
member, people_in_need, donation. It seems that I am focusing on creating this middle table with 3 keys and I can't think of solution because of it.
Any ideas? My design so far is really simple at the moment:
Donor (DonorID, Name, Age) Donations (DonationID, Amount) Member (MemberID, Name) PeopleInNeed (PeopleID, Name)
My best design was to create a relationship between
Donation where I have
Donor as FK in
Donation , and then I created 'middle' table
Action where I have composite key
(MemberID, DonationID, PeopleID, Date). Now problem is that same donor can place more than one donation in
Action and that is really the only problem I am facing.
Actually, one particular action may be related to only one people in need. I meant that one people in need can have more that one different actions for them. That is way I put both
PeopleID as PK's in