database design questions

is this a "good" design?

[xysrl]

Caution: complete newbie

I'm building a database for a non-profit organization. They need to track volunteers, contributions, events, bills, etc., including the ability to run queries/reports on the data. Here are the tables I've "designed"--my questions follow at the end:

Contacts
1.ContactID
2.ContactTitle
3.LastName
4.FirstName
5.ContactAddress1
6.ContactAddress2
7.ContactCity
8.ContactState
9.ContactZip
10.ContactHPhone
11.ContactWPhone
12.ContactCPhone
13.ContactEmail1
14.ContactEmail2
15.SubDate
16.ContactNotes
17.BizID (if the person works at a biz)

Businesses (note there are no human names: the idea is that if a $$ came from a business, the human name would go into contacts and everything else would go in this Biz table [cuz the person might be a donor as an individual, not just the person at the company who disperses checks] so a query for contributions by individual and contributions by biz would be properly separate...I hope)
1.BizID
2.BizName
3.BizPosition
4.BizAddress1
5.BizAddress2
6.BizCity
7.BizState
8.BizZip
9.BizPhone
10.BizTollFreePhone
11.BizWebSite
12.BizEmail1
13.BizEmail2
14.SubDate
15.BizNotes

Roles (volunteer, donor, media, etc.)
1.RoleID
2.RoleName
3.RoleDescription

Contributions
1.ContribID
2.ContribDate
3.ContribAmount
4.ContribSource (event, website, in-person)
5.ContribType (check, credit, cash)
6.ContribNotes

Interests (planting, cleanup, renovations, etc.)
1.InterestID
2.InterestName
3.InterestDescription

Events
1.EventID
2.EventName
3.EventDate
4.EventTime
5.EventLocation
6.EventType (fundraiser, cleanup, planting, etc.)
7.EventDescription

Media
1.MediaID
2.MediaName
3.MediaAuthor
4.MediaPhotographer
5.MediaFormat (magazine, newspaper, etc.)
6.MediaPieceLink (link to on-line piece)
7.MediaAuthorEmail
8.MediaWebsite
9.MediaDescription

Bills
1.PurchaseID
2.PurchaseItem
3.PurchaseQuantity
4.PurchaseCost
5.PurchaseDate
6.PurchaseType (credit card, cash, check, etc.)
7.PurchaseNeed
8.PurchaseApproval
9.PurchaseDescription
10.BizID

Then a slew of JOIN tables (just having the two respective ...IDs)
1.Contacts_Contributions
2.Contacts_Roles
3.Contacts_Interests
4.Contacts_Events
5.Contacts_Businesses
6.Events_Bills
7.Events_Contributions
8.Events_Media
9.Biz_Contributions

Question #1: Is this usable? Are there any flaws that will prohibit the kind of queries and reports a non-profit org might need?

Question #2: when inputting data via a form, how does the data get to the correct tables? That is, I have a contact who's interested in "planting" and "landscaping". I checkmark those two entries and click "Submit".
1. how does the contact data get put into the contact table and the two interests into the interest table, and
2. how does the information stay "connected"? Does it create ONE contact record with (generated?) ContactID and TWO records in Interests, each with its own ID, then TWO records are created in their JOIN table: ContactID + InterestID#1 and ContactID + InterestID#2

The second question reveals just how newbie newbie can be!

Thanks much for your input.

[maximillianos]

The backend programming is what inserts the data into the tables. It is dependent on what technology you are using. For instance, if you use PHP, then you would have your web form "post" to your PHP script, which in turn would take the data and update the appropriate tables.

Where the data gets updated/inserted is up to you and how you code it.

Are you building this yourself? If so, I would recommend getting some assistance from a seasoned developer until you get comfortable.

As for your database design, it is hard to say what is good or not without knowing the requirements of the system. For instance many of the join tables may not even be needed depending on how the data is being used.

Good luck!

[xysrl]

Thanks very much. The backend is the other subject I'm tackling--Ruby on Rails.
Wanna talk about feeling overwhelmed? :)

[whoisgregg]

Question #1: Is this usable? Are there any flaws that will prohibit the kind of queries and reports a non-profit org might need?

That is definitely usable. There's parts of it that aren't 3rd normal form, but for web databases you often flatten some of the model anyway to reduce the number of queries needed or to reduce the amount of data included in complex joins.

2. how does the information stay "connected"?

Essentially, it isn't connected except by your backend programming. You can very easily break the connections between different records if you accidentally change a foreign key value or lose a row in a join table.

I'll second maximillianos suggestion to get a seasoned developer to help with this. If your first project with a new back-end language has more than a few tables, it's going to be very difficult to manage that complexity. At the very least, consider building it incrementally as a series of smaller projects.

Good luck with this new project! :)

[xysrl]

Thank you, too.

I'm amazed how quickly this little database project to track volunteers and donations expanded into a not-so-little project.

I'm going to try to develop and test on my own pc using Tomcat as my web server (this might change), MySQL database and ruby on rails for the backend. I can't afford a seasoned developer to coach me through all this. I'm learninig by the seat of my pants with what I can find on the web.

I'm grateful for both your input that the design is workable (before I begin that aspect) and the clarification of what a "backend" is and does.

I don't understand how a join table magically routes the appropriate info to the appropriate table, but I suspect that's a backend thing. I'll figure it out eventually.

Thanks again

[whoisgregg]

I don't understand how a join table magically routes the appropriate info to the appropriate table, but I suspect that's a backend thing. I'll figure it out eventually.

I'm not familiar with Ruby, but in PHP it's not very magical. During record creation, you manage all those keys "manually" using a process like:

1. insert new contact record
2. store last inserted contact record ID in variable
3. insert new business record
4. store last inserted business record ID in variable
5. insert new join record using the contact record ID and business record ID

Pulling the data is easier because MySQL handles some of the complexity. You'll use the "known" data (like the contact record ID of the current contact) to perform a database join across tables. That in pseudocode could end up looking something like:

SELECT * FROM contact, business, join_contact_business
WHERE contact.contact_id = 1
AND contact.contact_id = join_contact_business.contact_id_f
AND business.business_id = join_contact_business.business_id_f

Added: While you're figuring it out, feel free to post here. I think most of us started right where you are, having a project that we had to learn as we went. :)

[xysrl]

Thanks, again. Your 1-5 process explanation turned on the lights for me, big time.

But I see I was getting ahead of myself. Sounds like the proper learning sequence is to implement my database design, populated it with data and get comfortable with all the query commands I need for reporting.
Then learn the backend and tie website posts to insertions to the appropriate database locations.
How's that commercial go? Easy peasy. :)