0

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.

4

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 -> Members
    PeopleID          FK2 -> 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 -> Donors
    ActionID     PK   FK2 -> Actions
    DonationDate
    Amount

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

|improve this answer|||||
  • 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. – nhrnjic6 Apr 7 '16 at 9:33
  • I've edited the answer. It doesn't change the proposed design for Donation. – ypercubeᵀᴹ Apr 7 '16 at 9:43
  • Yap, and the funny thing is that I have the solution you gave as 'on of the possibilities' but for some reason I thought its not gonna work. Thank you very much. – nhrnjic6 Apr 7 '16 at 10:07
0

I suggest to declare a Clustered Primary Key on Action like this:

ALTER TABLE Action 
  ADD CONSTRAINT PK_join_Profile_Tags 
  PRIMARY KEY CLUSTERED (MemberID,DonationID) 
GO

This way every duplicate on these pair will fail, so you need to make sure that in application you prevent duplications.
Or every insert from inside the SQL should be inside try...catch routine

|improve this answer|||||
  • 1
    CLUSTERED, GO and try...catch are only valid for SQL Server. The question is tagged with database-design, it doesn't target a specific DBMS. – ypercubeᵀᴹ Apr 7 '16 at 10:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.