Saints Rewards: Part 2 – Database Schema

This is part two of a series about the process of what we do as the Interactive Media Group. In case you missed Part 1, you can find it here: Saints Rewards: Part 1 – Introduction.

This is probably my favorite part in the entire process of creating a new application: setting up the database schema! Whoo-hoo, right! I know, I can’t wait either! For those looking for the regular sarcasm, I’m being serious. Database design is fun and challenging, and I enjoy it. Maybe I should see a support group…

Anyway, here is a quick glance of what I have mocked up for the database tables so far (ignore the chicken scratch!). I envision 4 main tables to store the data for this project.

The Event table will be pretty standard. The numPoints field may seem a little strange, but I want to make this extendable where the administrators can change point values for a certain event. I’ll also setup a default value that can be configured by the administrator. One thing I’m changing from the previous version is adding a category (from Event Category table) to the Event. I thought it might be beneficial to see what kind of events students are attending most often. This way we can run reports to find out if students are attending athletic events more than CAB events for example.

Developer Note: In most of my tables, I add a boolean field (true/false) called active to determine if an item has been removed by the user or not. I’ve been stung more than once where someone has deleted something and immediately wanted it back. So, now all I have to do is go back into the database and mark that item as active again. Bingo bango!

The Event_Participant_Joiner table is a common practice in programming to tie two different objects together. In this case, we are tying an Event and Participant (or student) together. This will keep track of the eventID, studentID and number of points (numPoints) in one place. Typically, I would not include numPoints because it’s already stored in the Event table and we could look it up based on the eventID field, but they wanted the ability to customize a participant’s number of points based on circumstances (if they are dressed up or extra spirited, for example).

The Level table is out in lala land currently. I see this as being calculated on the fly and just being for reference. As a student’s ID card is scanned, it will be grabbing all of the records from the Event_Participant_Joiner table based on their studentID. It will then calculate the sum of all the numPoints fields returned. If they level up, they will be notified via email. You may have noticed we have a field named emailText. This field will be used to generate the actual email sent to the student when they level up. I have included the imageUrl field which I’m hoping to be able to use by having Katrina create badges for the different levels.

The student data cloud in the middle of the image is an existing set of data that we have accessible to pull certain things (name, email, etc). All of the orange lines point to foreign keys in another table.

I hope this has been helpful. If you have questions, fire up the ol’ comment box down below!

Move It Along!