Campaign DB Normalization Input request

[lespaul]

Hello all my fellow database aficionados. I am in the midst of developing a campaign management database and have a normalization issue to put out for debate and input.

I'm wondering about the best way to keep track of individuals, companies and donations. My intial idea was:

tblPeople
PeopleID (PK)
FirstName
LastName
Address1
Address2
City
State
ZipCode
PreferredContactType (FK - tblContactInformation)

tblContactInformation
ContactID (PK)
PeopleID (FK - tblPeople)
ContactType (FK - tblContactCodes)
ContactInfo

tblContactCodes
ContactID
ContactDesc (hmphone, wkphone, email, cell)

tblCompanies
CompanyID (PK)
CompanyName
Address1
Address2
City
State
ZipCode
PrimaryContact (FK - tblPeople)

tblDonations
DonationID (PK)
DonatorID (FK - this is where I started to rethink design)
DonationDate
DonationAmt
DonationTender (FK - tblTenderTypes)

tblTenderTypes
TenderID
TenderDesc (visa, cash, check)

tblReferral
ReferralID (PK)
ReferredPeopleID (FK - tblPeople)
ReferredByID (FK - tblPeople)

When I got to tblDonations and started thinking about how to include both tblCompanies and tblPeople as the FK to DonatorID, and decided I would ask my esteemed colleagues how they would handle the situation.

Should I just have one table - tblContact and store both companies and people? Although thinking about it, I may even have to be able to separate corporate donations from personal contributions for campaign reporting requirements. If so, should I have two fields in tblDonations one to store PeopleID and one to store CompanyID? Or maybe add a field to the existing tblDonationonationType as a FK to tblDonationCodes that indicates whether it's a personal contribution or a corporate?

Thanks for any input!

leslie

 

[IsThisOneTaken2]

I'd seriously consider using the "Parties Model", which states that since for the purpose of this database, persons and companies are the same thing: Donors, or Parties, they could be put in the same table:
tblParties.

This model works great in lots of circumstances. Since a customer is sometimes also a vendor, and might even be a shipper, or an employee, and the point is to store data in only one place. Should we really have a Vendors table and a Customers table, and an Employees table? We'd rather make a change of address in only one place, regardless of the number of relationships we have to that party.

We need to have, then a tblRelationships.

FromParty_ID
FromRelationshipType
ToParty_ID
ToRelationshipType

This allows us to state, for example, that

Carly Fiorina is an
Employee of
Hewlett-Packard
Employer

And that

Hewlett-Packard is a
Vendor of
My Company - now the
Customer

You will need constraints to prevent some one from accidentally entering the reverse of a relationship that already exists. For example, if you have entered that

John Doe
Employee
My Company
Employer

You don't want to also allow

My Company
Employer
John Doe
Employee

But it is so cool to identify a new entity you need to track and see how elegant it can be done, for example, you decide you need to start tracking Emergency Contacts for employees, just create a new entry in tblRelationshipTypes, and add rows to tblRelationships.

 

[lespaul]

Richard? Jeremy? John? CC? Golom?

Anybody else? No thoughts or suggestions?

Les

 

[BSman]

From your initial description I wasn't sure if you need to have people without companies and companies without people. If companies will not be "on their own" (i.e., making donations without any person's name associated with the company), it could be somewhat involved. But I'd basically recommend having a company table that lists the info about each comapny and includes an ID (can be an autonumber column).

Your donor column would like each person like you do now, but also include the company ID. If there is no associated company, then the company ID would be null. You could even automatically fill in the address from the company info, but allow changes to be made for that person (like "5th floor, Accounting&quot within the company. Or, if you also need to capture the person's home address, just have an additional field for directing the communications within the company to that person.

You might also want to include a column where you can select the person's position in the company.

I'm not sure what you count as a "personal donation" versus a "company donation". A simple version would be that if it is from an identified person, it's a personal donation. But that depends upon the methods used to get donations and how you want to give the credit for successful fund raising.

With the single primary table that lists the people, you can easily find total donations by company and by individuals not associated with a company, once you've decided what consititues a company donation and what are individual donations. You could also handle true company donations by just entering a separate record in the persons table that either has no name or has the company name (and still refers to the company ID in the company table).

Your donations table would reference the record in the persons table to identify where the donation came from. You can then make a wide variety of reports depending upon what is needed.