Table Design: common attributes in multiple entities

[pv]

Hello All,



I am in real need of suggestions for best approach. The database I am
re-designing tracks donations which can be 1x donations related to
fundraising activities (annual fund, capital campaign), events, memberships,
or recurring donations as related to memberships, pledges (resulting from
camp fundraising activities or multi year membership), grants. I am
specifically concerned with fields common in each of the donations, pledges
and memberships and grants tables



Data flow - 1 time donations, with the exception of a grant donation is
entered directly in Donations with the associated fundraising activity if
appropriate.

Grants are entered in to grants table and then amount received is entered in
Donations table.

Memberships are entered in Membership table. 1 yr donations are entered in
donations. If it is a multi yr membership the additional membership dues are
tracked as a pledge with pledge payment schedule. When dues are received the
amount is entered into the donation table. Partial payments for dues are
accepted for both 1st year and additional year(s). Not all memberships are
paid memberships with related donation record.

Grants are entered into Grants table amount received is entered in donations
table.

Pledges (related to fundraising activity) Pledge entry and pledge payment
schedule records entered. Payments are entered in Donations table. Table
structure is listed below



In each of the tables are fields common to all (a one time donation will
have same related information as pledge, etc):



MatchedBy- appears in Membership, Pledge, Donation,

AppliedTo - appears in Pledges, donations, Grants

PaidThru Pledges, donations, Grants, and similar to field "GiftFrom" in
Membership.



I am struggling with the best way to set up the table structure, and how to
avoid redundant data entry in the donations table where and donation entry
is related to a pledge, or membership v.s. a one time donation where
MatchedBy, AppliedTo or Paid Thru information is collected. Or conversely
should the information be brought forward into the donation table from
membership or pledge or grants table. I am also concerned with queries but
assume that I could use a sql join between all table to query on MatchedBy,
AppliedTo or PaidThru. Add guess I would have to do the same thing if I
want to see all donors who have made one time donations and also have
pledged to make a donation. Is there an easier more efficient way?



Any suggestions as to how to resolve the above or to set me on a better path
is greatly appreciated.



Memberships (0,1 membership to many donation payments)

MemberID

Memberlevel

StartDate

EndDate

Upgrade

Comp

GiftFrom

MatchedBy




Pledges (1 pledge 1 to many donation payments, 1 donation can be related to
many scheduled payments)

PledgeID

PledgeType (can be multiyear membership or Fundraising Campaign)

CampFundID (ie Annual Fund & Date of Capital Campaign

AppliedTo

PaidThru

MatchedBy

PldgDate

PldgSched

PayableOver

SendStatement

AuthName

PublicRec

PldgPmtNote

CampFundID (fk to campfund lookup table with camp name and date)

MemberID (fk to Member table with membership details)






PledgePaymentSchedule (1pledge 1 to many payments scheduled)

PledgePaymentId

PldgSchedDate

PldgAmount (amount of individual payments)

PmtRecd

PmtNote

PldgID







Grants: (1 grant 1 to many donations)

GrantID

GrantName

GrantDate

Multi (# of payments to be made 1+)

Frequency

AppliedTo
TotalAmount (Total Amount of Multipayment grants)

DonorID









Donations

DonationID

DonorID

DatePaid

Amount

DonType

DonNote

GiftTo (Id of person who was receiptiant of donation, ie membership)

MatchedBy
AppliedTo

PaidThru

PldgID (FK Pledge)

MembershipID (FK Membership

EventID (FK Events)

GrantID (FK Grants)

 

[Allen Browne]

You do need *one* table where you enter all monies received, regardless of
whether they are donations, pledge payments, ticket purchases for an event,
or grants or whatever. This table might have several foreign keys (linking
to a pledge or event, for example) that you just leave blank where it is not
a pledge payment or event ticket.

At the simplest level, you need these tables:
- Client (one record per donor. Handles individuals and companies.)
- Pledge (one record for each pledge or membership)
- Donation (one record for each payment received.)
- DonationDetail (the $ in a donation, broken down by how applied.)

If you can assume that pledges will be the same $ equally spaced, you can
avoid the PledgeSchedule table. Instead, the Pledge table can include
fields:
PeriodID Text "yyyy", "m", "d", etc.
Freq Number how many periods
You can then calculate when pledge payments are due with:
DateAdd([PeriodID], [Freq], [PledgeStartDate])
and use a Cartesian product to generate the records within the term of the
pledge. If you are not familiar with using an unjoined "counter" table to do
this, there's an example here that explains how to get multiple records for
a label:
http://allenbrowne.com/ser-39.html
(At very least, you can use that approach to generate the records for the
PledgeSchedule when you enter the pledge entry.)

Taking that approach, membership payments are just a particular type of
recurring pledge. (BTW, the Pledge table also needs a field to limit the
number of payments too.)

I'm not sure how your grants work, but it may be that they could also be
handled as a type of "pledge."

The Pledge table now contains all the monies that should come in, and the
Donation table contains all the monies that did come in. It is therefore
easy to query for things like outstanding payments, or generate cashflow
projections.

BTW, the suggestion of a DonationDetail table is to cope with the recording
of monies received if clients could pay more than one thing at a time. For
example, if someone could send a check that covers their annual membership
plus some tickets for an event, the single donation would have 2 rows in the
DonationDetail table. Particularly if you have tax deductibility for some
things (like donations) but not others (such as sales where the client
receives a benefit), this is important.

 

[pv]

Hello Allen,

Thank you so much for the response.

As mentioned, not all memberships(specifically 1 year memberships) are
considered to be a pledge, only the 2nd or 3rd year of a multiyear
membership is considered to be a pledge. At this point all memberships are
for 1 year. And the business user does not see grants as a "pledge" since a
grant is a donation that may have multiple payments, but is guaranteed,
whereas a pledge is only a promise of donation. However, I get your point.
All monies to be received will be tracked as a pledge vs. all monies
received as tracked in donations, regardless of the business definition of a
pledge. The two tables would be linked in a donation detail table with the
following fields: DonationID, EventID, PledgeID

I do need to track details of the membership i.e. membership level and
description, if the membership is complimentary, start date and end date,
and if the membership was a gift who gave the gift, since they will be
credited for the donation. So think I will need a Membership table and the
data flow would be from membership to pledge to pledge payment, and the
membership record would be associated to the pledge record via foreign key
in the pledge table. Is this correct or would this information now be
tracked in the pledge table? I don't know if the user will go for entering
both the membership record and the pledge record for the single year
memberships.

Pledges are not be the same $ and are not equally spaced. In fact they could
be different amounts and different time increments. So thought the schedule
would just have to be manually entered. I need to confirm the payment
schedule for multi-year memberships, they may be more structured. If so
would you then still recommend that the member payment schedule is tracked
in the Pledge table or to include in the Membership table with method
described below.

BTW- I would also still need the applied field, since this field is used to
track if an annual fund or capital campaign donation is used in a specific
manner. I don't think I made this clear.

Just to clarify:
I see the importance of the donation detail table. But if a donation is
received that should be applied against an event and a pledge, then wouldn't
two separate donations - two separate donation amounts be needed in order to
track the Amount Pledged vs. amount outstanding on the pledge? How would
you handle knowing what was to be applied to pledge payment vs. the event
ticket if entered as a single donation amount.

Is the field need that is needed in the Pledge table "...to limit the number
of payments..." a number field with the number of payments or a date field
setting the term of the pledge. Hope I am not being too dense with this
question.

To your point re: donations that are result of sales. Events can be
auctions where items are sold. The sale amount of the item is record in a
separate table that tracks the auction items, the purchaser is associated to
the item and item price by a bidder number which is assigned and record in a
bidder table. Should I write the amount of the item(s) purchased to the
donations table in order to simplify queries?

Thank you for the link to explanation of how to get multiple records for a
label and your detailed explanation.

You do need *one* table where you enter all monies received, regardless of
whether they are donations, pledge payments, ticket purchases for an
event, or grants or whatever. This table might have several foreign keys
(linking to a pledge or event, for example) that you just leave blank
where it is not a pledge payment or event ticket.

At the simplest level, you need these tables:
- Client (one record per donor. Handles individuals and companies.)
- Pledge (one record for each pledge or membership)
- Donation (one record for each payment received.)
- DonationDetail (the $ in a donation, broken down by how applied.)

If you can assume that pledges will be the same $ equally spaced, you can
avoid the PledgeSchedule table. Instead, the Pledge table can include
fields:
PeriodID Text "yyyy", "m", "d", etc.
Freq Number how many periods
You can then calculate when pledge payments are due with:
DateAdd([PeriodID], [Freq], [PledgeStartDate])
and use a Cartesian product to generate the records within the term of the
pledge. If you are not familiar with using an unjoined "counter" table to
do this, there's an example here that explains how to get multiple records
for a label:
http://allenbrowne.com/ser-39.html
(At very least, you can use that approach to generate the records for the
PledgeSchedule when you enter the pledge entry.)

Taking that approach, membership payments are just a particular type of
recurring pledge. (BTW, the Pledge table also needs a field to limit the
number of payments too.)

I'm not sure how your grants work, but it may be that they could also be
handled as a type of "pledge."

The Pledge table now contains all the monies that should come in, and the
Donation table contains all the monies that did come in. It is therefore
easy to query for things like outstanding payments, or generate cashflow
projections.

BTW, the suggestion of a DonationDetail table is to cope with the
recording of monies received if clients could pay more than one thing at a
time. For example, if someone could send a check that covers their annual
membership plus some tickets for an event, the single donation would have
2 rows in the DonationDetail table. Particularly if you have tax
deductibility for some things (like donations) but not others (such as
sales where the client receives a benefit), this is important.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello All,

I am in real need of suggestions for best approach. The database I am
re-designing tracks donations which can be 1x donations related to
fundraising activities (annual fund, capital campaign), events,
memberships, or recurring donations as related to memberships, pledges
(resulting from camp fundraising activities or multi year membership),
grants. I am specifically concerned with fields common in each of the
donations, pledges and memberships and grants tables



Data flow - 1 time donations, with the exception of a grant donation is
entered directly in Donations with the associated fundraising activity if
appropriate.

Grants are entered in to grants table and then amount received is entered
in Donations table.

Memberships are entered in Membership table. 1 yr donations are entered
in donations. If it is a multi yr membership the additional membership
dues are tracked as a pledge with pledge payment schedule. When dues are
received the amount is entered into the donation table. Partial payments
for dues are accepted for both 1st year and additional year(s). Not all
memberships are paid memberships with related donation record.

Grants are entered into Grants table amount received is entered in
donations table.

Pledges (related to fundraising activity) Pledge entry and pledge payment
schedule records entered. Payments are entered in Donations table. Table
structure is listed below



In each of the tables are fields common to all (a one time donation will
have same related information as pledge, etc):



MatchedBy- appears in Membership, Pledge, Donation,

AppliedTo - appears in Pledges, donations, Grants

PaidThru Pledges, donations, Grants, and similar to field "GiftFrom" in
Membership.



I am struggling with the best way to set up the table structure, and how
to avoid redundant data entry in the donations table where and donation
entry is related to a pledge, or membership v.s. a one time donation
where MatchedBy, AppliedTo or Paid Thru information is collected. Or
conversely should the information be brought forward into the donation
table from membership or pledge or grants table. I am also concerned
with queries but assume that I could use a sql join between all table to
query on MatchedBy, AppliedTo or PaidThru. Add guess I would have to do
the same thing if I want to see all donors who have made one time
donations and also have pledged to make a donation. Is there an easier
more efficient way?



Any suggestions as to how to resolve the above or to set me on a better
path is greatly appreciated.



Memberships (0,1 membership to many donation payments)

MemberID

Memberlevel

StartDate

EndDate

Upgrade

Comp

GiftFrom

MatchedBy




Pledges (1 pledge 1 to many donation payments, 1 donation can be related
to many scheduled payments)

PledgeID

PledgeType (can be multiyear membership or Fundraising Campaign)

CampFundID (ie Annual Fund & Date of Capital Campaign

AppliedTo

PaidThru

MatchedBy

PldgDate

PldgSched

PayableOver

SendStatement

AuthName

PublicRec

PldgPmtNote

CampFundID (fk to campfund lookup table with camp name and date)

MemberID (fk to Member table with membership details)






PledgePaymentSchedule (1pledge 1 to many payments scheduled)

PledgePaymentId

PldgSchedDate

PldgAmount (amount of individual payments)

PmtRecd

PmtNote

PldgID







Grants: (1 grant 1 to many donations)

GrantID

GrantName

GrantDate

Multi (# of payments to be made 1+)

Frequency

AppliedTo
TotalAmount (Total Amount of Multipayment grants)

DonorID









Donations

DonationID

DonorID

DatePaid

Amount

DonType

DonNote

GiftTo (Id of person who was receiptiant of donation, ie membership)

MatchedBy
AppliedTo

PaidThru

PldgID (FK Pledge)

MembershipID (FK Membership

EventID (FK Events)

GrantID (FK Grants)

 

[Allen Browne]

Responses in-line.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello Allen,

Thank you so much for the response.

As mentioned, not all memberships(specifically 1 year memberships) are
considered to be a pledge, only the 2nd or 3rd year of a multiyear
membership is considered to be a pledge. At this point all memberships
are for 1 year. And the business user does not see grants as a "pledge"
since a grant is a donation that may have multiple payments, but is
guaranteed, whereas a pledge is only a promise of donation. However, I
get your point. All monies to be received will be tracked as a pledge vs.
all monies received as tracked in donations, regardless of the business
definition of a pledge. The two tables would be linked in a donation
detail table with the following fields: DonationID, EventID, PledgeID

Fair enough: a one-off payment for a membership that ends after that does
not require a Pledge entry.

I do need to track details of the membership i.e. membership level and
description, if the membership is complimentary, start date and end date,
and if the membership was a gift who gave the gift, since they will be
credited for the donation. So think I will need a Membership table and
the data flow would be from membership to pledge to pledge payment, and
the membership record would be associated to the pledge record via foreign
key in the pledge table. Is this correct or would this information now be
tracked in the pledge table? I don't know if the user will go for
entering both the membership record and the pledge record for the single
year memberships.

You are closer to your data, so you can decide what you really need. All I
can do is suggest possibilities. I would urge you to think of the data as:
a) Promises to pay, and
b) Payments received.

The promise to pay may be optional and unenforcable (like a pledge) or
guaranteed (like a multi-stage grant.) Either way, I would conceive of them
as the same kind of entity, and put them in the same table. If the pledge
can have a schedule of non-regular payments, then you will need the related
PledgeSchedule table.

The payments received may be for memberships, grants, events, whatever.
Again, I would conceive of the actual income as one kind of entity and have
just one table for monies received (regardless of whether they are pledge
payments, grant payments, sales, or memberships.)

If you need an additional membership table to handle the one-to-many
relations, then that's what you must do.

Pledges are not be the same $ and are not equally spaced. In fact they
could be different amounts and different time increments. So thought the
schedule would just have to be manually entered. I need to confirm the
payment schedule for multi-year memberships, they may be more structured.
If so would you then still recommend that the member payment schedule is
tracked in the Pledge table or to include in the Membership table with
method described below.

Use a PledgeSchedule table (as above.)

BTW- I would also still need the applied field, since this field is used
to track if an annual fund or capital campaign donation is used in a
specific manner. I don't think I made this clear.

Yes, that makes good sense.

Just to clarify:
I see the importance of the donation detail table. But if a donation is
received that should be applied against an event and a pledge, then
wouldn't two separate donations - two separate donation amounts be needed
in order to track the Amount Pledged vs. amount outstanding on the pledge?
How would you handle knowing what was to be applied to pledge payment vs.
the event ticket if entered as a single donation amount.

If you use a DonationDetail table, the Donation table does not have an
Amount field. The Amount goes into the detail table, and the amoutn of the
donation is the sum of amounts in the related table.

You may not want a DonationDetail table. Where it's handy is if the
application needs to do the banking as well. The main table indicates the
payment came from Fred Bloggs as check 987. The related table indicates how
you apportioned it.

Is the field need that is needed in the Pledge table "...to limit the
number of payments..." a number field with the number of payments or a
date field setting the term of the pledge. Hope I am not being too dense
with this question.

Yes: this is a number field. If someone pledges a one-off payment, the
number of payments is 1.

You do not need this field if you have a PaymentSchedule table: the number
of related records there indicates the number of payments.

To your point re: donations that are result of sales. Events can be
auctions where items are sold. The sale amount of the item is record in a
separate table that tracks the auction items, the purchaser is associated
to the item and item price by a bidder number which is assigned and record
in a bidder table. Should I write the amount of the item(s) purchased to
the donations table in order to simplify queries?

Again, you have the 2 main types of entities here:
- the promise to pay (contracted by the winning bidder), and
- the actual payment received (when the winning bidder actually pays.)
I imagine you need to track these 2 things separately.

In my way of thinking, they seem to be logically they are similar entities
to the Pledges and Donations above, though you may be aware of other
factors.

Thank you for the link to explanation of how to get multiple records for a
label and your detailed explanation.

You do need *one* table where you enter all monies received, regardless
of whether they are donations, pledge payments, ticket purchases for an
event, or grants or whatever. This table might have several foreign keys
(linking to a pledge or event, for example) that you just leave blank
where it is not a pledge payment or event ticket.

At the simplest level, you need these tables:
- Client (one record per donor. Handles individuals and companies.)
- Pledge (one record for each pledge or membership)
- Donation (one record for each payment received.)
- DonationDetail (the $ in a donation, broken down by how applied.)

If you can assume that pledges will be the same $ equally spaced, you can
avoid the PledgeSchedule table. Instead, the Pledge table can include
fields:
PeriodID Text "yyyy", "m", "d", etc.
Freq Number how many periods
You can then calculate when pledge payments are due with:
DateAdd([PeriodID], [Freq], [PledgeStartDate])
and use a Cartesian product to generate the records within the term of
the pledge. If you are not familiar with using an unjoined "counter"
table to do this, there's an example here that explains how to get
multiple records for a label:
http://allenbrowne.com/ser-39.html
(At very least, you can use that approach to generate the records for the
PledgeSchedule when you enter the pledge entry.)

Taking that approach, membership payments are just a particular type of
recurring pledge. (BTW, the Pledge table also needs a field to limit the
number of payments too.)

I'm not sure how your grants work, but it may be that they could also be
handled as a type of "pledge."

The Pledge table now contains all the monies that should come in, and the
Donation table contains all the monies that did come in. It is therefore
easy to query for things like outstanding payments, or generate cashflow
projections.

BTW, the suggestion of a DonationDetail table is to cope with the
recording of monies received if clients could pay more than one thing at
a time. For example, if someone could send a check that covers their
annual membership plus some tickets for an event, the single donation
would have 2 rows in the DonationDetail table. Particularly if you have
tax deductibility for some things (like donations) but not others (such
as sales where the client receives a benefit), this is important.

Hello All,

I am in real need of suggestions for best approach. The database I am
re-designing tracks donations which can be 1x donations related to
fundraising activities (annual fund, capital campaign), events,
memberships, or recurring donations as related to memberships, pledges
(resulting from camp fundraising activities or multi year membership),
grants. I am specifically concerned with fields common in each of the
donations, pledges and memberships and grants tables



Data flow - 1 time donations, with the exception of a grant donation is
entered directly in Donations with the associated fundraising activity
if appropriate.

Grants are entered in to grants table and then amount received is
entered in Donations table.

Memberships are entered in Membership table. 1 yr donations are
entered in donations. If it is a multi yr membership the additional
membership dues are tracked as a pledge with pledge payment schedule.
When dues are received the amount is entered into the donation table.
Partial payments for dues are accepted for both 1st year and additional
year(s). Not all memberships are paid memberships with related donation
record.

Grants are entered into Grants table amount received is entered in
donations table.

Pledges (related to fundraising activity) Pledge entry and pledge
payment schedule records entered. Payments are entered in Donations
table. Table structure is listed below



In each of the tables are fields common to all (a one time donation will
have same related information as pledge, etc):



MatchedBy- appears in Membership, Pledge, Donation,

AppliedTo - appears in Pledges, donations, Grants

PaidThru Pledges, donations, Grants, and similar to field "GiftFrom"
in Membership.



I am struggling with the best way to set up the table structure, and how
to avoid redundant data entry in the donations table where and donation
entry is related to a pledge, or membership v.s. a one time donation
where MatchedBy, AppliedTo or Paid Thru information is collected. Or
conversely should the information be brought forward into the donation
table from membership or pledge or grants table. I am also concerned
with queries but assume that I could use a sql join between all table to
query on MatchedBy, AppliedTo or PaidThru. Add guess I would have to do
the same thing if I want to see all donors who have made one time
donations and also have pledged to make a donation. Is there an easier
more efficient way?


Any suggestions as to how to resolve the above or to set me on a better
path is greatly appreciated.



Memberships (0,1 membership to many donation payments)

MemberID

Memberlevel

StartDate

EndDate

Upgrade

Comp

GiftFrom

MatchedBy



Pledges (1 pledge 1 to many donation payments, 1 donation can be related
to many scheduled payments)

PledgeID

PledgeType (can be multiyear membership or Fundraising Campaign)

CampFundID (ie Annual Fund & Date of Capital Campaign

AppliedTo

PaidThru

MatchedBy

PldgDate

PldgSched

PayableOver

SendStatement

AuthName

PublicRec

PldgPmtNote

CampFundID (fk to campfund lookup table with camp name and date)

MemberID (fk to Member table with membership details)



PledgePaymentSchedule (1pledge 1 to many payments scheduled)

PledgePaymentId

PldgSchedDate

PldgAmount (amount of individual payments)

PmtRecd

PmtNote

PldgID



Grants: (1 grant 1 to many donations)

GrantID

GrantName

GrantDate

Multi (# of payments to be made 1+)

Frequency

AppliedTo
TotalAmount (Total Amount of Multipayment grants)

DonorID



Donations

DonationID

DonorID

DatePaid

Amount

DonType

DonNote

GiftTo (Id of person who was receiptiant of donation, ie membership)

MatchedBy
AppliedTo

PaidThru

PldgID (FK Pledge)

MembershipID (FK Membership

EventID (FK Events)

GrantID (FK Grants)

 

[pv]

Again thank you. Your advice makes complete sense. I will let you know if
you if I have more specific questions as I work with this.

Responses in-line.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello Allen,

Thank you so much for the response.

As mentioned, not all memberships(specifically 1 year memberships) are
considered to be a pledge, only the 2nd or 3rd year of a multiyear
membership is considered to be a pledge. At this point all memberships
are for 1 year. And the business user does not see grants as a "pledge"
since a grant is a donation that may have multiple payments, but is
guaranteed, whereas a pledge is only a promise of donation. However, I
get your point. All monies to be received will be tracked as a pledge vs.
all monies received as tracked in donations, regardless of the business
definition of a pledge. The two tables would be linked in a donation
detail table with the following fields: DonationID, EventID, PledgeID

Fair enough: a one-off payment for a membership that ends after that does
not require a Pledge entry.

I do need to track details of the membership i.e. membership level and
description, if the membership is complimentary, start date and end date,
and if the membership was a gift who gave the gift, since they will be
credited for the donation. So think I will need a Membership table and
the data flow would be from membership to pledge to pledge payment, and
the membership record would be associated to the pledge record via
foreign key in the pledge table. Is this correct or would this
information now be tracked in the pledge table? I don't know if the user
will go for entering both the membership record and the pledge record for
the single year memberships.

You are closer to your data, so you can decide what you really need. All I
can do is suggest possibilities. I would urge you to think of the data as:
a) Promises to pay, and
b) Payments received.

The promise to pay may be optional and unenforcable (like a pledge) or
guaranteed (like a multi-stage grant.) Either way, I would conceive of
them as the same kind of entity, and put them in the same table. If the
pledge can have a schedule of non-regular payments, then you will need the
related PledgeSchedule table.

The payments received may be for memberships, grants, events, whatever.
Again, I would conceive of the actual income as one kind of entity and
have just one table for monies received (regardless of whether they are
pledge payments, grant payments, sales, or memberships.)

If you need an additional membership table to handle the one-to-many
relations, then that's what you must do.

Pledges are not be the same $ and are not equally spaced. In fact they
could be different amounts and different time increments. So thought the
schedule would just have to be manually entered. I need to confirm the
payment schedule for multi-year memberships, they may be more structured.
If so would you then still recommend that the member payment schedule is
tracked in the Pledge table or to include in the Membership table with
method described below.

Use a PledgeSchedule table (as above.)

BTW- I would also still need the applied field, since this field is used
to track if an annual fund or capital campaign donation is used in a
specific manner. I don't think I made this clear.

Yes, that makes good sense.

Just to clarify:
I see the importance of the donation detail table. But if a donation is
received that should be applied against an event and a pledge, then
wouldn't two separate donations - two separate donation amounts be needed
in order to track the Amount Pledged vs. amount outstanding on the
pledge? How would you handle knowing what was to be applied to pledge
payment vs. the event ticket if entered as a single donation amount.

If you use a DonationDetail table, the Donation table does not have an
Amount field. The Amount goes into the detail table, and the amoutn of the
donation is the sum of amounts in the related table.

You may not want a DonationDetail table. Where it's handy is if the
application needs to do the banking as well. The main table indicates the
payment came from Fred Bloggs as check 987. The related table indicates
how you apportioned it.

Is the field need that is needed in the Pledge table "...to limit the
number of payments..." a number field with the number of payments or a
date field setting the term of the pledge. Hope I am not being too dense
with this question.

Yes: this is a number field. If someone pledges a one-off payment, the
number of payments is 1.

You do not need this field if you have a PaymentSchedule table: the number
of related records there indicates the number of payments.

To your point re: donations that are result of sales. Events can be
auctions where items are sold. The sale amount of the item is record in
a separate table that tracks the auction items, the purchaser is
associated to the item and item price by a bidder number which is
assigned and record in a bidder table. Should I write the amount of the
item(s) purchased to the donations table in order to simplify queries?

Again, you have the 2 main types of entities here:
- the promise to pay (contracted by the winning bidder), and
- the actual payment received (when the winning bidder actually pays.)
I imagine you need to track these 2 things separately.

In my way of thinking, they seem to be logically they are similar entities
to the Pledges and Donations above, though you may be aware of other
factors.

Thank you for the link to explanation of how to get multiple records for
a label and your detailed explanation.

You do need *one* table where you enter all monies received, regardless
of whether they are donations, pledge payments, ticket purchases for an
event, or grants or whatever. This table might have several foreign keys
(linking to a pledge or event, for example) that you just leave blank
where it is not a pledge payment or event ticket.

At the simplest level, you need these tables:
- Client (one record per donor. Handles individuals and companies.)
- Pledge (one record for each pledge or membership)
- Donation (one record for each payment received.)
- DonationDetail (the $ in a donation, broken down by how applied.)

If you can assume that pledges will be the same $ equally spaced, you
can avoid the PledgeSchedule table. Instead, the Pledge table can
include fields:
PeriodID Text "yyyy", "m", "d", etc.
Freq Number how many periods
You can then calculate when pledge payments are due with:
DateAdd([PeriodID], [Freq], [PledgeStartDate])
and use a Cartesian product to generate the records within the term of
the pledge. If you are not familiar with using an unjoined "counter"
table to do this, there's an example here that explains how to get
multiple records for a label:
http://allenbrowne.com/ser-39.html
(At very least, you can use that approach to generate the records for
the PledgeSchedule when you enter the pledge entry.)

Taking that approach, membership payments are just a particular type of
recurring pledge. (BTW, the Pledge table also needs a field to limit the
number of payments too.)

I'm not sure how your grants work, but it may be that they could also be
handled as a type of "pledge."

The Pledge table now contains all the monies that should come in, and
the Donation table contains all the monies that did come in. It is
therefore easy to query for things like outstanding payments, or
generate cashflow projections.

BTW, the suggestion of a DonationDetail table is to cope with the
recording of monies received if clients could pay more than one thing at
a time. For example, if someone could send a check that covers their
annual membership plus some tickets for an event, the single donation
would have 2 rows in the DonationDetail table. Particularly if you have
tax deductibility for some things (like donations) but not others (such
as sales where the client receives a benefit), this is important.

Hello All,

I am in real need of suggestions for best approach. The database I am
re-designing tracks donations which can be 1x donations related to
fundraising activities (annual fund, capital campaign), events,
memberships, or recurring donations as related to memberships,
pledges (resulting from camp fundraising activities or multi year
membership), grants. I am specifically concerned with fields common in
each of the donations, pledges and memberships and grants tables



Data flow - 1 time donations, with the exception of a grant donation is
entered directly in Donations with the associated fundraising activity
if appropriate.

Grants are entered in to grants table and then amount received is
entered in Donations table.

Memberships are entered in Membership table. 1 yr donations are
entered in donations. If it is a multi yr membership the additional
membership dues are tracked as a pledge with pledge payment schedule.
When dues are received the amount is entered into the donation table.
Partial payments for dues are accepted for both 1st year and additional
year(s). Not all memberships are paid memberships with related
donation record.

Grants are entered into Grants table amount received is entered in
donations table.

Pledges (related to fundraising activity) Pledge entry and pledge
payment schedule records entered. Payments are entered in Donations
table. Table structure is listed below



In each of the tables are fields common to all (a one time donation
will have same related information as pledge, etc):



MatchedBy- appears in Membership, Pledge, Donation,

AppliedTo - appears in Pledges, donations, Grants

PaidThru Pledges, donations, Grants, and similar to field "GiftFrom"
in Membership.



I am struggling with the best way to set up the table structure, and
how to avoid redundant data entry in the donations table where and
donation entry is related to a pledge, or membership v.s. a one time
donation where MatchedBy, AppliedTo or Paid Thru information is
collected. Or conversely should the information be brought forward
into the donation table from membership or pledge or grants table. I
am also concerned with queries but assume that I could use a sql join
between all table to query on MatchedBy, AppliedTo or PaidThru. Add
guess I would have to do the same thing if I want to see all donors who
have made one time donations and also have pledged to make a donation.
Is there an easier more efficient way?


Any suggestions as to how to resolve the above or to set me on a better
path is greatly appreciated.



Memberships (0,1 membership to many donation payments)

MemberID

Memberlevel

StartDate

EndDate

Upgrade

Comp

GiftFrom

MatchedBy



Pledges (1 pledge 1 to many donation payments, 1 donation can be
related to many scheduled payments)

PledgeID

PledgeType (can be multiyear membership or Fundraising Campaign)

CampFundID (ie Annual Fund & Date of Capital Campaign

AppliedTo

PaidThru

MatchedBy

PldgDate

PldgSched

PayableOver

SendStatement

AuthName

PublicRec

PldgPmtNote

CampFundID (fk to campfund lookup table with camp name and date)

MemberID (fk to Member table with membership details)



PledgePaymentSchedule (1pledge 1 to many payments scheduled)

PledgePaymentId

PldgSchedDate

PldgAmount (amount of individual payments)

PmtRecd

PmtNote

PldgID



Grants: (1 grant 1 to many donations)

GrantID

GrantName

GrantDate

Multi (# of payments to be made 1+)

Frequency

AppliedTo
TotalAmount (Total Amount of Multipayment grants)

DonorID



Donations

DonationID

DonorID

DatePaid

Amount

DonType

DonNote

GiftTo (Id of person who was receiptiant of donation, ie membership)

MatchedBy
AppliedTo

PaidThru

PldgID (FK Pledge)

MembershipID (FK Membership

EventID (FK Events)

GrantID (FK Grants)

 

[pv]

Hi Allen,

I have been working with your suggestion consider the data as "monies to
come in" and "monies received".
I have come to the point where I am considering capturing all transactions
with money to be received in one table, could be called "Transactions" with
field a "Transaction Type" to indicate what the money to be received. A
transaction could be related to an event, pledge, membership, 1 time
donation of an unknown source that can be applied in 1 or many ways.
Still a little uncertain if I will need the Donations Detail table (yes,
money comes in that does need to be applied to two items). This is the
structure I am thinking of:

Client (one record per donor, handles both individuals and companies):

Transactions:
TransactionID
TransactionDate
TransactionType (Event, Pledge, Membership, Grant, Sponsorship(someone
sponsors an event), Donation (1x time donation of an unknown source which
can be applied 1+ ways)
EventID (fk to reference table with event details - EventID, Event Name,
Event Date)
CampId (fk to reference table with Camp fundraising events. CampID,
CampName, CampDate
MembershipLevelID (FK to reference table of membership levels)
MembershipStartDate
MembershipEndDate
PaidBy (memberships are given as gifts)
MembershipType (Pd or comlimentary)
AppliedTo (relates to one time donation of unknown source, or pledge, or
grant transactions)
Frequency (single, Annual, Monthly, Other)
Payable Over (relates to re-occuring tranasactions)
Send Statment (relates to re-occuring transactions)
AuthName (relates to pledge transactions)
PubRec (relates to pledge transactions)

PaymentSchedule: (Handles PledgePayment or multiyear membership payments)
PaymentScheduleID
TransactionID


Donations:
DonationID
TransactionID
Amount
Note

Can you point out the negatives or gotchas with this approach? I do see
that each record will have many fields with null values. I am not only
looking to track monies to be received vs. monies received, but will also
need to report on event attendance, generate mailing lists based on past
event attendance as well as pledges and donations.

Again your help and patience is greatly appreciated.
Thank you

Responses in-line.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello Allen,

Thank you so much for the response.

As mentioned, not all memberships(specifically 1 year memberships) are
considered to be a pledge, only the 2nd or 3rd year of a multiyear
membership is considered to be a pledge. At this point all memberships
are for 1 year. And the business user does not see grants as a "pledge"
since a grant is a donation that may have multiple payments, but is
guaranteed, whereas a pledge is only a promise of donation. However, I
get your point. All monies to be received will be tracked as a pledge vs.
all monies received as tracked in donations, regardless of the business
definition of a pledge. The two tables would be linked in a donation
detail table with the following fields: DonationID, EventID, PledgeID

Fair enough: a one-off payment for a membership that ends after that does
not require a Pledge entry.

I do need to track details of the membership i.e. membership level and
description, if the membership is complimentary, start date and end date,
and if the membership was a gift who gave the gift, since they will be
credited for the donation. So think I will need a Membership table and
the data flow would be from membership to pledge to pledge payment, and
the membership record would be associated to the pledge record via
foreign key in the pledge table. Is this correct or would this
information now be tracked in the pledge table? I don't know if the user
will go for entering both the membership record and the pledge record for
the single year memberships.

You are closer to your data, so you can decide what you really need. All I
can do is suggest possibilities. I would urge you to think of the data as:
a) Promises to pay, and
b) Payments received.

The promise to pay may be optional and unenforcable (like a pledge) or
guaranteed (like a multi-stage grant.) Either way, I would conceive of
them as the same kind of entity, and put them in the same table. If the
pledge can have a schedule of non-regular payments, then you will need the
related PledgeSchedule table.

The payments received may be for memberships, grants, events, whatever.
Again, I would conceive of the actual income as one kind of entity and
have just one table for monies received (regardless of whether they are
pledge payments, grant payments, sales, or memberships.)

If you need an additional membership table to handle the one-to-many
relations, then that's what you must do.

Pledges are not be the same $ and are not equally spaced. In fact they
could be different amounts and different time increments. So thought the
schedule would just have to be manually entered. I need to confirm the
payment schedule for multi-year memberships, they may be more structured.
If so would you then still recommend that the member payment schedule is
tracked in the Pledge table or to include in the Membership table with
method described below.

Use a PledgeSchedule table (as above.)

BTW- I would also still need the applied field, since this field is used
to track if an annual fund or capital campaign donation is used in a
specific manner. I don't think I made this clear.

Yes, that makes good sense.

Just to clarify:
I see the importance of the donation detail table. But if a donation is
received that should be applied against an event and a pledge, then
wouldn't two separate donations - two separate donation amounts be needed
in order to track the Amount Pledged vs. amount outstanding on the
pledge? How would you handle knowing what was to be applied to pledge
payment vs. the event ticket if entered as a single donation amount.

If you use a DonationDetail table, the Donation table does not have an
Amount field. The Amount goes into the detail table, and the amoutn of the
donation is the sum of amounts in the related table.

You may not want a DonationDetail table. Where it's handy is if the
application needs to do the banking as well. The main table indicates the
payment came from Fred Bloggs as check 987. The related table indicates
how you apportioned it.

Is the field need that is needed in the Pledge table "...to limit the
number of payments..." a number field with the number of payments or a
date field setting the term of the pledge. Hope I am not being too dense
with this question.

Yes: this is a number field. If someone pledges a one-off payment, the
number of payments is 1.

You do not need this field if you have a PaymentSchedule table: the number
of related records there indicates the number of payments.

To your point re: donations that are result of sales. Events can be
auctions where items are sold. The sale amount of the item is record in
a separate table that tracks the auction items, the purchaser is
associated to the item and item price by a bidder number which is
assigned and record in a bidder table. Should I write the amount of the
item(s) purchased to the donations table in order to simplify queries?

Again, you have the 2 main types of entities here:
- the promise to pay (contracted by the winning bidder), and
- the actual payment received (when the winning bidder actually pays.)
I imagine you need to track these 2 things separately.

In my way of thinking, they seem to be logically they are similar entities
to the Pledges and Donations above, though you may be aware of other
factors.

Thank you for the link to explanation of how to get multiple records for
a label and your detailed explanation.

You do need *one* table where you enter all monies received, regardless
of whether they are donations, pledge payments, ticket purchases for an
event, or grants or whatever. This table might have several foreign keys
(linking to a pledge or event, for example) that you just leave blank
where it is not a pledge payment or event ticket.

At the simplest level, you need these tables:
- Client (one record per donor. Handles individuals and companies.)
- Pledge (one record for each pledge or membership)
- Donation (one record for each payment received.)
- DonationDetail (the $ in a donation, broken down by how applied.)

If you can assume that pledges will be the same $ equally spaced, you
can avoid the PledgeSchedule table. Instead, the Pledge table can
include fields:
PeriodID Text "yyyy", "m", "d", etc.
Freq Number how many periods
You can then calculate when pledge payments are due with:
DateAdd([PeriodID], [Freq], [PledgeStartDate])
and use a Cartesian product to generate the records within the term of
the pledge. If you are not familiar with using an unjoined "counter"
table to do this, there's an example here that explains how to get
multiple records for a label:
http://allenbrowne.com/ser-39.html
(At very least, you can use that approach to generate the records for
the PledgeSchedule when you enter the pledge entry.)

Taking that approach, membership payments are just a particular type of
recurring pledge. (BTW, the Pledge table also needs a field to limit the
number of payments too.)

I'm not sure how your grants work, but it may be that they could also be
handled as a type of "pledge."

The Pledge table now contains all the monies that should come in, and
the Donation table contains all the monies that did come in. It is
therefore easy to query for things like outstanding payments, or
generate cashflow projections.

BTW, the suggestion of a DonationDetail table is to cope with the
recording of monies received if clients could pay more than one thing at
a time. For example, if someone could send a check that covers their
annual membership plus some tickets for an event, the single donation
would have 2 rows in the DonationDetail table. Particularly if you have
tax deductibility for some things (like donations) but not others (such
as sales where the client receives a benefit), this is important.

Hello All,

I am in real need of suggestions for best approach. The database I am
re-designing tracks donations which can be 1x donations related to
fundraising activities (annual fund, capital campaign), events,
memberships, or recurring donations as related to memberships,
pledges (resulting from camp fundraising activities or multi year
membership), grants. I am specifically concerned with fields common in
each of the donations, pledges and memberships and grants tables



Data flow - 1 time donations, with the exception of a grant donation is
entered directly in Donations with the associated fundraising activity
if appropriate.

Grants are entered in to grants table and then amount received is
entered in Donations table.

Memberships are entered in Membership table. 1 yr donations are
entered in donations. If it is a multi yr membership the additional
membership dues are tracked as a pledge with pledge payment schedule.
When dues are received the amount is entered into the donation table.
Partial payments for dues are accepted for both 1st year and additional
year(s). Not all memberships are paid memberships with related
donation record.

Grants are entered into Grants table amount received is entered in
donations table.

Pledges (related to fundraising activity) Pledge entry and pledge
payment schedule records entered. Payments are entered in Donations
table. Table structure is listed below



In each of the tables are fields common to all (a one time donation
will have same related information as pledge, etc):



MatchedBy- appears in Membership, Pledge, Donation,

AppliedTo - appears in Pledges, donations, Grants

PaidThru Pledges, donations, Grants, and similar to field "GiftFrom"
in Membership.



I am struggling with the best way to set up the table structure, and
how to avoid redundant data entry in the donations table where and
donation entry is related to a pledge, or membership v.s. a one time
donation where MatchedBy, AppliedTo or Paid Thru information is
collected. Or conversely should the information be brought forward
into the donation table from membership or pledge or grants table. I
am also concerned with queries but assume that I could use a sql join
between all table to query on MatchedBy, AppliedTo or PaidThru. Add
guess I would have to do the same thing if I want to see all donors who
have made one time donations and also have pledged to make a donation.
Is there an easier more efficient way?


Any suggestions as to how to resolve the above or to set me on a better
path is greatly appreciated.



Memberships (0,1 membership to many donation payments)

MemberID

Memberlevel

StartDate

EndDate

Upgrade

Comp

GiftFrom

MatchedBy



Pledges (1 pledge 1 to many donation payments, 1 donation can be
related to many scheduled payments)

PledgeID

PledgeType (can be multiyear membership or Fundraising Campaign)

CampFundID (ie Annual Fund & Date of Capital Campaign

AppliedTo

PaidThru

MatchedBy

PldgDate

PldgSched

PayableOver

SendStatement

AuthName

PublicRec

PldgPmtNote

CampFundID (fk to campfund lookup table with camp name and date)

MemberID (fk to Member table with membership details)



PledgePaymentSchedule (1pledge 1 to many payments scheduled)

PledgePaymentId

PldgSchedDate

PldgAmount (amount of individual payments)

PmtRecd

PmtNote

PldgID



Grants: (1 grant 1 to many donations)

GrantID

GrantName

GrantDate

Multi (# of payments to be made 1+)

Frequency

AppliedTo
TotalAmount (Total Amount of Multipayment grants)

DonorID



Donations

DonationID

DonorID

DatePaid

Amount

DonType

DonNote

GiftTo (Id of person who was receiptiant of donation, ie membership)

MatchedBy
AppliedTo

PaidThru

PldgID (FK Pledge)

MembershipID (FK Membership

EventID (FK Events)

GrantID (FK Grants)

 

[Allen Browne]

The single Transaction table sounds good.

The multiple foreign keys (EventID, CampID, etc) is sometimes the best
approach. And BTW, you can still interface this in the way you were talking
about, it. e.g. a Camp main form with the transactions in its subform, an
Event main form with transactions in its subform, ...

You might also like to search on "subclass". This is a technique where you
have one main table (like you Transaction), with the vastly different fields
for the different types each stored in their own tables. Use the Advanced
Search at groups.google.com, in the microsoft.public.access.* groups.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

I have been working with your suggestion consider the data as "monies to
come in" and "monies received".
I have come to the point where I am considering capturing all
transactions with money to be received in one table, could be called
"Transactions" with field a "Transaction Type" to indicate what the money
to be received. A transaction could be related to an event, pledge,
membership, 1 time donation of an unknown source that can be applied in 1
or many ways.
Still a little uncertain if I will need the Donations Detail table (yes,
money comes in that does need to be applied to two items). This is the
structure I am thinking of:

Client (one record per donor, handles both individuals and companies):

Transactions:
TransactionID
TransactionDate
TransactionType (Event, Pledge, Membership, Grant, Sponsorship(someone
sponsors an event), Donation (1x time donation of an unknown source which
can be applied 1+ ways)
EventID (fk to reference table with event details - EventID, Event Name,
Event Date)
CampId (fk to reference table with Camp fundraising events. CampID,
CampName, CampDate
MembershipLevelID (FK to reference table of membership levels)
MembershipStartDate
MembershipEndDate
PaidBy (memberships are given as gifts)
MembershipType (Pd or comlimentary)
AppliedTo (relates to one time donation of unknown source, or pledge, or
grant transactions)
Frequency (single, Annual, Monthly, Other)
Payable Over (relates to re-occuring tranasactions)
Send Statment (relates to re-occuring transactions)
AuthName (relates to pledge transactions)
PubRec (relates to pledge transactions)

PaymentSchedule: (Handles PledgePayment or multiyear membership payments)
PaymentScheduleID
TransactionID


Donations:
DonationID
TransactionID
Amount
Note

Can you point out the negatives or gotchas with this approach? I do see
that each record will have many fields with null values. I am not only
looking to track monies to be received vs. monies received, but will also
need to report on event attendance, generate mailing lists based on past
event attendance as well as pledges and donations.

Again your help and patience is greatly appreciated.
Thank you

Responses in-line.

Hello Allen,

Thank you so much for the response.

As mentioned, not all memberships(specifically 1 year memberships) are
considered to be a pledge, only the 2nd or 3rd year of a multiyear
membership is considered to be a pledge. At this point all memberships
are for 1 year. And the business user does not see grants as a "pledge"
since a grant is a donation that may have multiple payments, but is
guaranteed, whereas a pledge is only a promise of donation. However, I
get your point. All monies to be received will be tracked as a pledge
vs. all monies received as tracked in donations, regardless of the
business definition of a pledge. The two tables would be linked in a
donation detail table with the following fields: DonationID, EventID,
PledgeID

Fair enough: a one-off payment for a membership that ends after that does
not require a Pledge entry.

I do need to track details of the membership i.e. membership level and
description, if the membership is complimentary, start date and end
date, and if the membership was a gift who gave the gift, since they
will be credited for the donation. So think I will need a Membership
table and the data flow would be from membership to pledge to pledge
payment, and the membership record would be associated to the pledge
record via foreign key in the pledge table. Is this correct or would
this information now be tracked in the pledge table? I don't know if
the user will go for entering both the membership record and the pledge
record for the single year memberships.

You are closer to your data, so you can decide what you really need. All
I can do is suggest possibilities. I would urge you to think of the data
as:
a) Promises to pay, and
b) Payments received.

The promise to pay may be optional and unenforcable (like a pledge) or
guaranteed (like a multi-stage grant.) Either way, I would conceive of
them as the same kind of entity, and put them in the same table. If the
pledge can have a schedule of non-regular payments, then you will need
the related PledgeSchedule table.

The payments received may be for memberships, grants, events, whatever.
Again, I would conceive of the actual income as one kind of entity and
have just one table for monies received (regardless of whether they are
pledge payments, grant payments, sales, or memberships.)

If you need an additional membership table to handle the one-to-many
relations, then that's what you must do.

Pledges are not be the same $ and are not equally spaced. In fact they
could be different amounts and different time increments. So thought
the schedule would just have to be manually entered. I need to confirm
the payment schedule for multi-year memberships, they may be more
structured. If so would you then still recommend that the member payment
schedule is tracked in the Pledge table or to include in the Membership
table with method described below.

Use a PledgeSchedule table (as above.)

BTW- I would also still need the applied field, since this field is used
to track if an annual fund or capital campaign donation is used in a
specific manner. I don't think I made this clear.

Yes, that makes good sense.

Just to clarify:
I see the importance of the donation detail table. But if a donation is
received that should be applied against an event and a pledge, then
wouldn't two separate donations - two separate donation amounts be
needed in order to track the Amount Pledged vs. amount outstanding on
the pledge? How would you handle knowing what was to be applied to
pledge payment vs. the event ticket if entered as a single donation
amount.

If you use a DonationDetail table, the Donation table does not have an
Amount field. The Amount goes into the detail table, and the amoutn of
the donation is the sum of amounts in the related table.

You may not want a DonationDetail table. Where it's handy is if the
application needs to do the banking as well. The main table indicates the
payment came from Fred Bloggs as check 987. The related table indicates
how you apportioned it.

Is the field need that is needed in the Pledge table "...to limit the
number of payments..." a number field with the number of payments or a
date field setting the term of the pledge. Hope I am not being too
dense with this question.

Yes: this is a number field. If someone pledges a one-off payment, the
number of payments is 1.

You do not need this field if you have a PaymentSchedule table: the
number of related records there indicates the number of payments.

To your point re: donations that are result of sales. Events can be
auctions where items are sold. The sale amount of the item is record in
a separate table that tracks the auction items, the purchaser is
associated to the item and item price by a bidder number which is
assigned and record in a bidder table. Should I write the amount of the
item(s) purchased to the donations table in order to simplify queries?

Again, you have the 2 main types of entities here:
- the promise to pay (contracted by the winning bidder), and
- the actual payment received (when the winning bidder actually pays.)
I imagine you need to track these 2 things separately.

In my way of thinking, they seem to be logically they are similar
entities to the Pledges and Donations above, though you may be aware of
other factors.

Thank you for the link to explanation of how to get multiple records for
a label and your detailed explanation.


You do need *one* table where you enter all monies received, regardless
of whether they are donations, pledge payments, ticket purchases for an
event, or grants or whatever. This table might have several foreign
keys (linking to a pledge or event, for example) that you just leave
blank where it is not a pledge payment or event ticket.

At the simplest level, you need these tables:
- Client (one record per donor. Handles individuals and companies.)
- Pledge (one record for each pledge or membership)
- Donation (one record for each payment received.)
- DonationDetail (the $ in a donation, broken down by how applied.)

If you can assume that pledges will be the same $ equally spaced, you
can avoid the PledgeSchedule table. Instead, the Pledge table can
include fields:
PeriodID Text "yyyy", "m", "d", etc.
Freq Number how many periods
You can then calculate when pledge payments are due with:
DateAdd([PeriodID], [Freq], [PledgeStartDate])
and use a Cartesian product to generate the records within the term of
the pledge. If you are not familiar with using an unjoined "counter"
table to do this, there's an example here that explains how to get
multiple records for a label:
http://allenbrowne.com/ser-39.html
(At very least, you can use that approach to generate the records for
the PledgeSchedule when you enter the pledge entry.)

Taking that approach, membership payments are just a particular type of
recurring pledge. (BTW, the Pledge table also needs a field to limit
the number of payments too.)

I'm not sure how your grants work, but it may be that they could also
be handled as a type of "pledge."

The Pledge table now contains all the monies that should come in, and
the Donation table contains all the monies that did come in. It is
therefore easy to query for things like outstanding payments, or
generate cashflow projections.

BTW, the suggestion of a DonationDetail table is to cope with the
recording of monies received if clients could pay more than one thing
at a time. For example, if someone could send a check that covers their
annual membership plus some tickets for an event, the single donation
would have 2 rows in the DonationDetail table. Particularly if you have
tax deductibility for some things (like donations) but not others (such
as sales where the client receives a benefit), this is important.

Hello All,

I am in real need of suggestions for best approach. The database I am
re-designing tracks donations which can be 1x donations related to
fundraising activities (annual fund, capital campaign), events,
memberships, or recurring donations as related to memberships,
pledges (resulting from camp fundraising activities or multi year
membership), grants. I am specifically concerned with fields common
in each of the donations, pledges and memberships and grants tables



Data flow - 1 time donations, with the exception of a grant donation
is entered directly in Donations with the associated fundraising
activity if appropriate.

Grants are entered in to grants table and then amount received is
entered in Donations table.

Memberships are entered in Membership table. 1 yr donations are
entered in donations. If it is a multi yr membership the additional
membership dues are tracked as a pledge with pledge payment schedule.
When dues are received the amount is entered into the donation table.
Partial payments for dues are accepted for both 1st year and
additional year(s). Not all memberships are paid memberships with
related donation record.

Grants are entered into Grants table amount received is entered in
donations table.

Pledges (related to fundraising activity) Pledge entry and pledge
payment schedule records entered. Payments are entered in Donations
table. Table structure is listed below



In each of the tables are fields common to all (a one time donation
will have same related information as pledge, etc):



MatchedBy- appears in Membership, Pledge, Donation,

AppliedTo - appears in Pledges, donations, Grants

PaidThru Pledges, donations, Grants, and similar to field "GiftFrom"
in Membership.



I am struggling with the best way to set up the table structure, and
how to avoid redundant data entry in the donations table where and
donation entry is related to a pledge, or membership v.s. a one time
donation where MatchedBy, AppliedTo or Paid Thru information is
collected. Or conversely should the information be brought forward
into the donation table from membership or pledge or grants table. I
am also concerned with queries but assume that I could use a sql join
between all table to query on MatchedBy, AppliedTo or PaidThru. Add
guess I would have to do the same thing if I want to see all donors
who have made one time donations and also have pledged to make a
donation. Is there an easier more efficient way?


Any suggestions as to how to resolve the above or to set me on a
better path is greatly appreciated.



Memberships (0,1 membership to many donation payments)

MemberID

Memberlevel

StartDate

EndDate

Upgrade

Comp

GiftFrom

MatchedBy



Pledges (1 pledge 1 to many donation payments, 1 donation can be
related to many scheduled payments)

PledgeID

PledgeType (can be multiyear membership or Fundraising Campaign)

CampFundID (ie Annual Fund & Date of Capital Campaign

AppliedTo

PaidThru

MatchedBy

PldgDate

PldgSched

PayableOver

SendStatement

AuthName

PublicRec

PldgPmtNote

CampFundID (fk to campfund lookup table with camp name and date)

MemberID (fk to Member table with membership details)



PledgePaymentSchedule (1pledge 1 to many payments scheduled)

PledgePaymentId

PldgSchedDate

PldgAmount (amount of individual payments)

PmtRecd

PmtNote

PldgID



Grants: (1 grant 1 to many donations)

GrantID

GrantName

GrantDate

Multi (# of payments to be made 1+)

Frequency

AppliedTo
TotalAmount (Total Amount of Multipayment grants)

DonorID



Donations

DonationID

DonorID

DatePaid

Amount

DonType

DonNote

GiftTo (Id of person who was receiptiant of donation, ie membership)

MatchedBy
AppliedTo

PaidThru

PldgID (FK Pledge)

MembershipID (FK Membership

EventID (FK Events)

GrantID (FK Grants)

 

[pv]

Thank you Allen. I think I am finally getting it.

Glad you mentioned that I can still interface with the transactions as
subform. I was thinking that the transaction form would need to be the
parent form.

I was also thinking of using subclass technique, and was just reviewing the
details and will continue to search/read.

The single Transaction table sounds good.

The multiple foreign keys (EventID, CampID, etc) is sometimes the best
approach. And BTW, you can still interface this in the way you were
talking about, it. e.g. a Camp main form with the transactions in its
subform, an Event main form with transactions in its subform, ...

You might also like to search on "subclass". This is a technique where you
have one main table (like you Transaction), with the vastly different
fields for the different types each stored in their own tables. Use the
Advanced Search at groups.google.com, in the microsoft.public.access.*
groups.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

I have been working with your suggestion consider the data as "monies to
come in" and "monies received".
I have come to the point where I am considering capturing all
transactions with money to be received in one table, could be called
"Transactions" with field a "Transaction Type" to indicate what the money
to be received. A transaction could be related to an event, pledge,
membership, 1 time donation of an unknown source that can be applied in 1
or many ways.
Still a little uncertain if I will need the Donations Detail table (yes,
money comes in that does need to be applied to two items). This is the
structure I am thinking of:

Client (one record per donor, handles both individuals and companies):

Transactions:
TransactionID
TransactionDate
TransactionType (Event, Pledge, Membership, Grant, Sponsorship(someone
sponsors an event), Donation (1x time donation of an unknown source which
can be applied 1+ ways)
EventID (fk to reference table with event details - EventID, Event Name,
Event Date)
CampId (fk to reference table with Camp fundraising events. CampID,
CampName, CampDate
MembershipLevelID (FK to reference table of membership levels)
MembershipStartDate
MembershipEndDate
PaidBy (memberships are given as gifts)
MembershipType (Pd or comlimentary)
AppliedTo (relates to one time donation of unknown source, or pledge, or
grant transactions)
Frequency (single, Annual, Monthly, Other)
Payable Over (relates to re-occuring tranasactions)
Send Statment (relates to re-occuring transactions)
AuthName (relates to pledge transactions)
PubRec (relates to pledge transactions)

PaymentSchedule: (Handles PledgePayment or multiyear membership payments)
PaymentScheduleID
TransactionID


Donations:
DonationID
TransactionID
Amount
Note

Can you point out the negatives or gotchas with this approach? I do see
that each record will have many fields with null values. I am not only
looking to track monies to be received vs. monies received, but will also
need to report on event attendance, generate mailing lists based on past
event attendance as well as pledges and donations.

Again your help and patience is greatly appreciated.
Thank you

Responses in-line.

Hello Allen,

Thank you so much for the response.

As mentioned, not all memberships(specifically 1 year memberships) are
considered to be a pledge, only the 2nd or 3rd year of a multiyear
membership is considered to be a pledge. At this point all memberships
are for 1 year. And the business user does not see grants as a
"pledge" since a grant is a donation that may have multiple payments,
but is guaranteed, whereas a pledge is only a promise of donation.
However, I get your point. All monies to be received will be tracked as
a pledge vs. all monies received as tracked in donations, regardless
of the business definition of a pledge. The two tables would be linked
in a donation detail table with the following fields: DonationID,
EventID, PledgeID

Fair enough: a one-off payment for a membership that ends after that
does not require a Pledge entry.

I do need to track details of the membership i.e. membership level and
description, if the membership is complimentary, start date and end
date, and if the membership was a gift who gave the gift, since they
will be credited for the donation. So think I will need a Membership
table and the data flow would be from membership to pledge to pledge
payment, and the membership record would be associated to the pledge
record via foreign key in the pledge table. Is this correct or would
this information now be tracked in the pledge table? I don't know if
the user will go for entering both the membership record and the pledge
record for the single year memberships.

You are closer to your data, so you can decide what you really need. All
I can do is suggest possibilities. I would urge you to think of the data
as:
a) Promises to pay, and
b) Payments received.

The promise to pay may be optional and unenforcable (like a pledge) or
guaranteed (like a multi-stage grant.) Either way, I would conceive of
them as the same kind of entity, and put them in the same table. If the
pledge can have a schedule of non-regular payments, then you will need
the related PledgeSchedule table.

The payments received may be for memberships, grants, events, whatever.
Again, I would conceive of the actual income as one kind of entity and
have just one table for monies received (regardless of whether they are
pledge payments, grant payments, sales, or memberships.)

If you need an additional membership table to handle the one-to-many
relations, then that's what you must do.


Pledges are not be the same $ and are not equally spaced. In fact they
could be different amounts and different time increments. So thought
the schedule would just have to be manually entered. I need to confirm
the payment schedule for multi-year memberships, they may be more
structured. If so would you then still recommend that the member
payment schedule is tracked in the Pledge table or to include in the
Membership table with method described below.

Use a PledgeSchedule table (as above.)

BTW- I would also still need the applied field, since this field is
used to track if an annual fund or capital campaign donation is used in
a specific manner. I don't think I made this clear.

Yes, that makes good sense.

Just to clarify:
I see the importance of the donation detail table. But if a donation
is received that should be applied against an event and a pledge, then
wouldn't two separate donations - two separate donation amounts be
needed in order to track the Amount Pledged vs. amount outstanding on
the pledge? How would you handle knowing what was to be applied to
pledge payment vs. the event ticket if entered as a single donation
amount.

If you use a DonationDetail table, the Donation table does not have an
Amount field. The Amount goes into the detail table, and the amoutn of
the donation is the sum of amounts in the related table.

You may not want a DonationDetail table. Where it's handy is if the
application needs to do the banking as well. The main table indicates
the payment came from Fred Bloggs as check 987. The related table
indicates how you apportioned it.

Is the field need that is needed in the Pledge table "...to limit the
number of payments..." a number field with the number of payments or a
date field setting the term of the pledge. Hope I am not being too
dense with this question.

Yes: this is a number field. If someone pledges a one-off payment, the
number of payments is 1.

You do not need this field if you have a PaymentSchedule table: the
number of related records there indicates the number of payments.

To your point re: donations that are result of sales. Events can be
auctions where items are sold. The sale amount of the item is record
in a separate table that tracks the auction items, the purchaser is
associated to the item and item price by a bidder number which is
assigned and record in a bidder table. Should I write the amount of
the item(s) purchased to the donations table in order to simplify
queries?

Again, you have the 2 main types of entities here:
- the promise to pay (contracted by the winning bidder), and
- the actual payment received (when the winning bidder actually pays.)
I imagine you need to track these 2 things separately.

In my way of thinking, they seem to be logically they are similar
entities to the Pledges and Donations above, though you may be aware of
other factors.

Thank you for the link to explanation of how to get multiple records
for a label and your detailed explanation.


You do need *one* table where you enter all monies received,
regardless of whether they are donations, pledge payments, ticket
purchases for an event, or grants or whatever. This table might have
several foreign keys (linking to a pledge or event, for example) that
you just leave blank where it is not a pledge payment or event ticket.

At the simplest level, you need these tables:
- Client (one record per donor. Handles individuals and companies.)
- Pledge (one record for each pledge or membership)
- Donation (one record for each payment received.)
- DonationDetail (the $ in a donation, broken down by how applied.)

If you can assume that pledges will be the same $ equally spaced, you
can avoid the PledgeSchedule table. Instead, the Pledge table can
include fields:
PeriodID Text "yyyy", "m", "d", etc.
Freq Number how many periods
You can then calculate when pledge payments are due with:
DateAdd([PeriodID], [Freq], [PledgeStartDate])
and use a Cartesian product to generate the records within the term of
the pledge. If you are not familiar with using an unjoined "counter"
table to do this, there's an example here that explains how to get
multiple records for a label:
http://allenbrowne.com/ser-39.html
(At very least, you can use that approach to generate the records for
the PledgeSchedule when you enter the pledge entry.)

Taking that approach, membership payments are just a particular type
of recurring pledge. (BTW, the Pledge table also needs a field to
limit the number of payments too.)

I'm not sure how your grants work, but it may be that they could also
be handled as a type of "pledge."

The Pledge table now contains all the monies that should come in, and
the Donation table contains all the monies that did come in. It is
therefore easy to query for things like outstanding payments, or
generate cashflow projections.

BTW, the suggestion of a DonationDetail table is to cope with the
recording of monies received if clients could pay more than one thing
at a time. For example, if someone could send a check that covers
their annual membership plus some tickets for an event, the single
donation would have 2 rows in the DonationDetail table. Particularly
if you have tax deductibility for some things (like donations) but not
others (such as sales where the client receives a benefit), this is
important.

Hello All,

I am in real need of suggestions for best approach. The database I
am re-designing tracks donations which can be 1x donations related to
fundraising activities (annual fund, capital campaign), events,
memberships, or recurring donations as related to memberships,
pledges (resulting from camp fundraising activities or multi year
membership), grants. I am specifically concerned with fields common
in each of the donations, pledges and memberships and grants tables



Data flow - 1 time donations, with the exception of a grant donation
is entered directly in Donations with the associated fundraising
activity if appropriate.

Grants are entered in to grants table and then amount received is
entered in Donations table.

Memberships are entered in Membership table. 1 yr donations are
entered in donations. If it is a multi yr membership the additional
membership dues are tracked as a pledge with pledge payment schedule.
When dues are received the amount is entered into the donation table.
Partial payments for dues are accepted for both 1st year and
additional year(s). Not all memberships are paid memberships with
related donation record.

Grants are entered into Grants table amount received is entered in
donations table.

Pledges (related to fundraising activity) Pledge entry and pledge
payment schedule records entered. Payments are entered in Donations
table. Table structure is listed below



In each of the tables are fields common to all (a one time donation
will have same related information as pledge, etc):



MatchedBy- appears in Membership, Pledge, Donation,

AppliedTo - appears in Pledges, donations, Grants

PaidThru Pledges, donations, Grants, and similar to field
"GiftFrom" in Membership.



I am struggling with the best way to set up the table structure, and
how to avoid redundant data entry in the donations table where and
donation entry is related to a pledge, or membership v.s. a one time
donation where MatchedBy, AppliedTo or Paid Thru information is
collected. Or conversely should the information be brought forward
into the donation table from membership or pledge or grants table.
I am also concerned with queries but assume that I could use a sql
join between all table to query on MatchedBy, AppliedTo or PaidThru.
Add guess I would have to do the same thing if I want to see all
donors who have made one time donations and also have pledged to make
a donation. Is there an easier more efficient way?


Any suggestions as to how to resolve the above or to set me on a
better path is greatly appreciated.



Memberships (0,1 membership to many donation payments)

MemberID

Memberlevel

StartDate

EndDate

Upgrade

Comp

GiftFrom

MatchedBy



Pledges (1 pledge 1 to many donation payments, 1 donation can be
related to many scheduled payments)

PledgeID

PledgeType (can be multiyear membership or Fundraising Campaign)

CampFundID (ie Annual Fund & Date of Capital Campaign

AppliedTo

PaidThru

MatchedBy

PldgDate

PldgSched

PayableOver

SendStatement

AuthName

PublicRec

PldgPmtNote

CampFundID (fk to campfund lookup table with camp name and date)

MemberID (fk to Member table with membership details)



PledgePaymentSchedule (1pledge 1 to many payments scheduled)

PledgePaymentId

PldgSchedDate

PldgAmount (amount of individual payments)

PmtRecd

PmtNote

PldgID



Grants: (1 grant 1 to many donations)

GrantID

GrantName

GrantDate

Multi (# of payments to be made 1+)

Frequency

AppliedTo
TotalAmount (Total Amount of Multipayment grants)

DonorID



Donations

DonationID

DonorID

DatePaid

Amount

DonType

DonNote

GiftTo (Id of person who was receiptiant of donation, ie membership)

MatchedBy
AppliedTo

PaidThru

PldgID (FK Pledge)

MembershipID (FK Membership

EventID (FK Events)

GrantID (FK Grants)