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!

Saints Rewards: Part 1 – Introduction

So, I’m looking at our blog and it is sorely out of date! I was just wondering today what I could write about to spice up the site a little bit.

The Brainchild

My goal is to give you kind folks an idea into our world and development cycle. I’m hoping for this to be a series of posts highlighting the process we go through when creating our “interactive magicness”.

The Project

Today, I met with some folks about implementing a rewards program for students that attend different events on campus. Luckily, I had already worked on something similar to this a couple of years ago for the Campus Activities Board (CAB). The previous application allowed someone with a laptop to scan student ID cards at campus events and the students would receive rewards after attending ‘x’ number of events.

So what’s changing? What’s the Twist?

The new application will be a points system to reward students for attending events on campus. It will need to have a leader board for the students to view and see where they are in the standings. As they attend more events they will increase their level (level up). I’m not sure at this point if they receive prizes as they level up. I believe there are prizes at the end of the contest for the top students.


As our department is responsible for the digital signs on campus, I think it would be a fun idea to create a couple of digital signs showing the leader boards and another showing upcoming events to earn points.


The existing application is written in ASP.NET/C#. We’ll be sticking with ASP.NET/C# for this new application and putting it on our intranet server.

Who’s Involved?

Kate will be involved with creating the digital signs using Adobe Flash. Katrina will be involved with creating any graphics needed for the project. I’ll be creating the database schema and doing the programming work involved for the back-end of this beast. Last but not least, Michael will be there for moral support!


The much anticipated launch date is January 2013.

So who’s with us? Anyone out there interested in a peek at our world?

Giddyup, what’s next?