Database design issue:

Question

Hey guys,

I'm building a Volunteer Management System and I'm having some DB design issues:

To explain the process: Volunteers can sign up for accounts. Volunteers report their hours to a project (each volunteer can have multiple projects). Volunteer supervisors are notified when a volunteers number of hours are close to some specified amount to give them a reward.

For example: a volunteer who has volunteered 10 hours receives a free t shirt.

The problem I'm having is how to design the DB in such a way that a single reward profile can be related to multiple projects as well as have a single reward profile be "multi-tiered". A big thing about this is that rewards structures may change so they can't be just hardcoded.

Example of what I mean by "multi-tiered" reward profile: A volunteer who has volunteered 10 hours receives a free t shirt. A volunteer who has volunteered 40 hours receives a free $50 appreciation check.

The solutions I've come up with myself are: To have a reward profile table that relates one row to each reward profile.

rewardprofile:
rID(primary key) - int
description - varchar / char(100)
details - varchar / file (XML)

Aside, just while on the topic, can DB field entries be files?

OR

To have a rewards table that relates one preset amount and reward where each row is as follows and a second rewards profile table that binds them the rewards entries together:

rewards:
rID(primary key) - int
rpID (references rewardsProfile) - int
numberOfHrs - int
rewardDesc - varchar / char(100)

rewardsprofile:
rpID(primary key) - int
description

so this might look something like:

rewardsprofile:
rpid | desc
rp01 | no reward
rp02 | t-shirt only
rp03 | t-shirt and check

rewards
rid | rpID | hours | desc
r01 | rp02 |  10   | t-shirt
r02 | rp03 |  10   | t-shirt
r03 | rp03 |  40   | check

I'm sure this issue is nothing new but my google fu is weak and I don't know how to phrase this in a meaningful way. I think there must be a solution out there more formalized than my (hack and slash) method. If anyone can direct me to what this problem is called or any solutions to it, that would be swell. Thanks for all your time!

Cheers, -Jeremiah Tantongco

Answer 1

Yes, database fields can be files (type binary, character large object, or xml) depending on the implementation of the specific database.

The rewardsprofile table looks like it might be challenging to maintain if you have a large number of different rewards in the future. One thing you might consider is a structure like:

rewards:
rID(primary key) - int
numberOfHrs - int
rewardDesc - varchar / char(100)

volunteers:
vID(primary key) - int
.. any other fields you want here ..

rewardshistory:
vID (foreign key references volunteers)
rID (foreign key references rewards)

Any time you want to add a reward, you add it to the rewards table. Old rewards stay in the table (you might want an 'current' field or something to track whether the reward can still be assigned). The rewardshistory table tracks which rewards have been given to what volunteers.

Answer 2

Yes, DB field entries can be files. Or, more precisely, they can be filespecs that reference files. Is that what you really meant?

While we are on the subject of data fields that reference other data, how much do you know about foreign keys? What can you accomplish with references to files that you couldn't accomplish even better by the judicious use of foreign keys?

Foreign keys, and the keys that they refer to, are fundamental concepts in the relational model of data. Without this model, your database design is going to be pretty random.

Answer 3

This is a rough structure of how I would handle this:

Volunteers
    volunteerid
    firstname
    lastname

VolunteerAddress
    volunteerid
    Street1
    Street2
    City
    State
    POstalcode
    Country
    Addresstype (home, business, etc.)

VolunteerPhone
    volunteerid
    Phone number
    Phonetype

VolunteerEmail  
    volunteerid 
    EmailAddress

Project
    Projectid
    projectname

VolunteerHours
    volunteerid
    hoursworked
    projectid
    DateWorked

Rewards
    Rewardid
    Rewardtype (Continual, datelimited, etc.)
    Reward
    RewardBeginDate
    RewardEndDate
         RequiredHours

Awarded
    VolunteerID
    RewardID
    RewardDate

You will probably have some time-limited rewards, that's why I added the date fields. You would then set up a job to calculate rewards once a week or once a month or so. Make sure to exclude those who have already receivced that particualr award if pertinent (You don't want to give a new t-shirt for every 10 hours worked do you?)