Design problem with placing proper constrains 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 : donors, donations, members, people_in_need. 

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 Donor and 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. 

Edit 2:

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 MemberID and PeopleID as PK's in Action table.
Lets start first with the part of the design that seems OK. You have Member and Donor and PeopleInNeed. It's all right to have 3 tables, one for each entity:

Donor 
    DonorID      PK
    Name
    Age 

Member 
    MemberID     PK
    Name 

PeopleInNeed 
    PeopleID     PK
    Name 


The problem is in the Action and Donation tables. You say:


Member can create many action for collecting donations and people in need can have many actions for them.


I take that to mean that Actions are created by a member (so a 1-to-many relationship between Member and Action).

Also that PeopleInNeed may be related to multiple Actions and an action may be related to multiple people in need (so a many-to-many relationship between PeopleInNeed and Actions.


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 MemberID and PeopleID as PK's in Action table. 


The Action table becomes:

Action 
    ActionID     PK
    MemberID          FK1 -&gt; Members
    PeopleID          FK2 -&gt; PeopleInNeed
    ActionCreatedDate  


Then we have Donations. You say:


Now the key thing is that donor can donate only once for a particular action.


That tells me that Donation is just a many-to-many table between Donor and Action.

Donation 
    DonorID      PK   FK1 -&gt; Donors
    ActionID     PK   FK2 -&gt; Actions
    DonationDate
    Amount


You could make the (DonorID, ActionID) a UNIQUE constraint instead, if you absolutely need a surrogate DonationID PK as well.