0

I'm working on a constituent relationship management system in Microsoft Access 2007. We have two types of contributors each with their own tables:

  1. The table Constituents, which has individual people with names and ages and a bunch of foreign keys to different contact information in different tables

  2. The table Organizations, which has fields about org-specific info.

Both Constituents and Organizations can make multiple contributions, which are represented by records in one of 3 tables Pledges, InKindDonations, and MonetaryContributions. These 3 tables can't be merged, they hold different types of info.

Allowing Organizations to make contributions will be a new feature in an existing database. Currently only Constituents can make contributions: each of the 3 contribution tables has a ConstituentId field which is a FK to the [Constituents].[ConstituentId] field.

I am having trouble coming up with a good way to link contributor records from Constituents & Organizations with contribution records while maintaining referential integrity using features available in Access, and making minimal changes to the many existing queries and procedures which reference the 5 tables.

My only idea is that I could add an OrgId field in each of the 3 contribution tables, which would be a FK to Organizations.OrgId -- but I would have to ensure that one and only one of the 2 FK fields ( ConstituentId & OrgId) exists for each contribution, and that they are valid FKs.

Is this a good solution or an ugly hack?

Ain Britain
  • 370
  • 4
  • 15

1 Answers1

1

Your solution will require minimum changes, however you won't be able to keep referential integrity, since referential integrity won't let you have a record in Pledges, InKindDonations, and MonetaryContributions unless you have a FK value in both Organizations and Constituents for every record. For example if you set referential integrity for Organizations and add a record for Constituents, the OrgId in the contributions tables will have to be blank which will be a violation of referential integrity for those tables with respect to the Organizations table.

If you still want to keep referential integrity, a different approach (requires modifications to many forms and reports), would be to add a ConstituentId to the Organizations table or add a OrgId to the Constituents table, you would have to modify reports and forms that list Organizations, Constituents, and contributions to distinguish between contributions made by people and contributions made by organizations.

In keeping the possibility for using referential integrity, you could also add a new Contributors table to and add a CcontributorId FK to Constituents and Organizations table, same as before you would have to make changes to reports, forms for listings and totals.

You should consider the last two options since there is a chance you would be asked to change/create reports that involve contributions by organizations as well.

Manuel Castro
  • 138
  • 6