table design issues - and suggestions

[pv]

I am hoping to get assistance on a database design which would appear to be
simple to track memberships, events, pledges, grants, and donations,
volunteers, artists. The difficulty appears in a requirement to track all
contacts by affiliation type as it may be related to memberships, donations,
and or events.



One Contact has 1 or more type of affiliation:

1 affiliation type may be related to 1 or more events, or 1 or more pledges,
or 1 or more donations

Examples of Affiliation type:

Volunteer

Artist

Foundation

Annual Fund (donor who has made a 1x donation or pledge with multiple
donations)

Capital Campaign Donor (donor who has made 1x donation or pledge with
multiple donations)

Funder (donor in which the donation funds an activity or related item i.e.
catalogue or has funded a grant)

Art Supporter - makes a donation that does not fall into the above
affiliation types



I assume that a members affiliation should not be stored in the affiliation
table since this status will change over time and plan to simply store most
current information on the contact record related to the membership.



1 donation may be related to 1 or more events and or pledges and or
memberships.

1 donations may NOT be related to event, pledge or membership, but the donor
should be assigned an affiliation. For example a Capital Campaign Donor who
makes a 1 time donation should have affiliation of Capital Campaign Donor
and Donation Record. An Art Supporter would also make a one time donation
that would not fall into the other categories.



So the table structure I have set up so far is:

Contact

ContactName, etc



tblContactAffiliations: (PK=ContactID & AffilID)

ContD

AffilTypeID

EffectiveDate



*one participant can participate in one event in more than one capacity.
For example as an event participant and as a corporate sponsor.



EventAffiliation (PK= ContactID, EventName, EventDate,AffiliationID

ContactID

EventName

EventDate

AffiliationID







tblEventsParticipants (PK =EventName &EventDate& ContID & AffilTypeID)

EventName

EventDate

ContactID (FK)

AffilTypeID (FK)



EventRole: (Pk =EventName & EventDate & ContID & EventRole)*

EventName

EventDate

ContID

EventRole (Attendee, Artist, Vol)

AffilTypeID (FK)

*One Contact can participate in one event in one or more roles: Attendee,
Volunteer, Artist



tblMemberships (PK - MembershipLevel and MembershipStartDate)

MembershipLevel

MembershipStartDate

ContactID



tblPledges(PK = PledgeID,ContID)

PledgeID

ContID

PldgeDate

Frequency (Annually, Monthly, other)

PayableOver (No of payments)



tblPledgePaymentSchedule: (PledgePmtID, ContID)

PledgePmtId

ContID

PaymentDate

PaymentRecieved (InFull, Partial, No)



tblGrants (PK GrantName and GrantDate)

GrantName

GrantDate

Multi (1,2,3) - this would record number of expected payments

Frequency (Annually, Monthly)

TotalAmountAwarded

AppliedTo





Donations (PK - DonationId)*

DonationID

DonationAmount

DonationType (ie. Gift, Corporate, Annual Fund, Capital Camp)

ApplyTo

*One Donation Amount may have many sources, ie cap camp or membership

*One Event may have 0 to many donations.

*one Membership may have 0 to many donations. Eventually all should have at
least 1.

*one Pledge Payment may have 0 to may donations. Eventually all should have
at least 1.





So may dilemma is the relationship between affiliation and events, pledges,
and grants.

It appears that I need a junction table from affiliations to events, pledges
and grants, but not certain how to set it up. If it should be a single
table or separate table for each relationship, ie

Event Affiliations ( as set up above) and PledgeAffiliations,
GrantAffiliations.



I see a similar situation between membership, events, pledges, grants and
donations as well as donations that do not track through these tables i.e. 1
time cash donation. I am thinking that a table something like
DonationSource where SourceID which could store the AffiliationID, EventID,
PledgePaymentID, or MembershipId and donationID. I am just have trouble
envisioning how this will work at the form level. Or should the donations
be stored in table specific to the membership, pledge payment,

Grant, events. If so then how are 1 time donations not related to one of
these items captured?



I am wondering it there is actually a better way of insuring that each
activity has an assigned affiliation without actually designing it into the
table structures, i.e. through vba. I have looked at this however it seems
the variables of future affiliations and what specific affiliation should be
assigned are not something that should be hard code.



I warmly welcome all suggestions and apologize for the long post and home my
explanation is clear.



Thank you very much for your assistance.

 

[Jamie Collins]

<<snipped>>
I am wondering it there is actually a better way of insuring that each
activity has an assigned affiliation without actually designing it into the
table structures, i.e. through vba.
<<snipped>>

There's a lot to take in here so I'll consider the above sentence in
isolation.

With the SQL language there is no good way to ensure rows exist in
multiple tables.

Consider a business rule that states that an order doesn't come into
existence without at least one order item (where the orderrder items
relationship is 1:m). Because in SQL the orders and order items are in
different tables, you have a catch-22 situation: can't create a row in
the Orders until a respective row has been created in the OrderItems
table; OrderItems REFERENCES Orders therefore requires a respective
row in Orders.

In standard SQL you could put a table-level CHECK constraint the
Orders table and declare it as deferrable; in usage, the constraint
could be deferred while rows are assigned to both tables, with the
constraint to be automatically checked when attempting to commit the
transaction. However, deferrable constraints are not supported in
Jet.

In theory, you should be able to create a VIEW joining the two tables
and INSERT into the VIEW because, according to the SQL-92 standard,
CHECK constraints get checked after each SQL *statement*. Sadly, due
to a bug in Jet its CHECK constraints are actually checked table-by-
table and row-by-row, so the constraint will bite too early

[Well, I assume it's a bug: Microsoft hasn't actually given any
details on how CHECK constraints *should* work in Jet. It seems a
reasonable assumption that they work to the SQL-92 spec but probably
ran into limitations of the Jet technology e.g. ingrained assumption
that 'table constraint' is synonymous with 'index'.]

As a workaround, you could drop then recreate the constraint in a
transaction (presumably resulting in a table lock) and reapply it but
there is no mechanism to ensure the constraint actually gets recreated
and giving users/applications schema privileges is not a great idea.
Anyhow, multiple SQL statements in a Jet SQL PROCEDURE is also not
supported, therefore it would be inevitable that VBA must be used at
some point with this approach.

From a Jet SQL point of view, my recommendation would be to create a

SQL PROCEDURE which creates an order plus its first order item by
inserting into a VIEW that joins both tables, then revoke the 'update'
privileges from the base tables to guide (i.e. force <g>) users to
only use the VIEW or the helper proc when creating an order.

FWIW one response I've seen to this problem was make the first order
item an attribute of the order entity (i.e. put the columns for the
first order item in the Orders table -- ouch), an approach I would not
recommend!

Jamie.

--

 

[pv]

Hi Jamie,
Thank you for the explanation. I will get back to you if I have specific
questions.

<<snipped>>
I am wondering it there is actually a better way of insuring that each
activity has an assigned affiliation without actually designing it into
the
table structures, i.e. through vba.
<<snipped>>

There's a lot to take in here so I'll consider the above sentence in
isolation.

With the SQL language there is no good way to ensure rows exist in
multiple tables.

Consider a business rule that states that an order doesn't come into
existence without at least one order item (where the orderrder items
relationship is 1:m). Because in SQL the orders and order items are in
different tables, you have a catch-22 situation: can't create a row in
the Orders until a respective row has been created in the OrderItems
table; OrderItems REFERENCES Orders therefore requires a respective
row in Orders.

In standard SQL you could put a table-level CHECK constraint the
Orders table and declare it as deferrable; in usage, the constraint
could be deferred while rows are assigned to both tables, with the
constraint to be automatically checked when attempting to commit the
transaction. However, deferrable constraints are not supported in
Jet.

In theory, you should be able to create a VIEW joining the two tables
and INSERT into the VIEW because, according to the SQL-92 standard,
CHECK constraints get checked after each SQL *statement*. Sadly, due
to a bug in Jet its CHECK constraints are actually checked table-by-
table and row-by-row, so the constraint will bite too early

[Well, I assume it's a bug: Microsoft hasn't actually given any
details on how CHECK constraints *should* work in Jet. It seems a
reasonable assumption that they work to the SQL-92 spec but probably
ran into limitations of the Jet technology e.g. ingrained assumption
that 'table constraint' is synonymous with 'index'.]

As a workaround, you could drop then recreate the constraint in a
transaction (presumably resulting in a table lock) and reapply it but
there is no mechanism to ensure the constraint actually gets recreated
and giving users/applications schema privileges is not a great idea.
Anyhow, multiple SQL statements in a Jet SQL PROCEDURE is also not
supported, therefore it would be inevitable that VBA must be used at
some point with this approach.

From a Jet SQL point of view, my recommendation would be to create a

SQL PROCEDURE which creates an order plus its first order item by
inserting into a VIEW that joins both tables, then revoke the 'update'
privileges from the base tables to guide (i.e. force <g>) users to
only use the VIEW or the helper proc when creating an order.

FWIW one response I've seen to this problem was make the first order
item an attribute of the order entity (i.e. put the columns for the
first order item in the Orders table -- ouch), an approach I would not
recommend!

Jamie.