Scholarship Database: An overview

An in-depth look at the construction of a scholarship database for the Office of Student Financial Aid at ISU.

So I’ve been working at the Office of Student Financial Aid for a little over a year now. Apart from other projects, I’ve been spending a lot of time creating an interface to the scholarship database used by several staff members to enter and search the entries. The interface has also been extended to the public website with some success.

Because I’ve never done anything serious with databases before, this has been quite an undertaking. Here’s an overview of what the database looks like:

SDB Screenshot

  • Scholarships table with unique, static information
  • Tables for categories, majors, classifications, states, counties, high school, etc
  • Tables for links between scholarship unique scholarships and unique category entries
When working with entries in their primary tables, the coding is pretty simple. However, when parsing through the links and connections between two (or more) tables, things get tricky. It normally results in an PHP loop similar to this: 
for ($x=0; $x<count($_POST['Category']); $x++)
 {
 $sql = "insert into CategoryLinks (ScholarshipID,CategoryID) values
 (
 '" . $ScholarshipID . "',
 '" . $_POST['Category'][$x] . "'
 )
 ";
 RunQuery($sql,$con);
 }
A large amount of data is entered per scholarship, and a large amount of links are made between each scholarship and it's respective category tables. The creation process goes smoothly, but when it comes time to filter the scholarships by category, things get trickier. I've been working on a scholarship search/filter function that allows the user to select a variety of details describing himself or herself (grade, GPA, location) to determine which scholarships will best suit him or her.

I’ve been running into a problem, however: it’s difficult to define which scholarship belongs to a category, which belongs to all categories, and which belongs to no categories. For example, if a scholarship is open to every major, would the user select every checkbox for every major during the creation process, or would they select none (because the scholarship has no major requirements)? I have yet to find a solution to this issue.

In the meantime, I’ve been working on a filter/search solution to help users pinpoint what entry they’re looking for. They can do a live search (example here: http://www.financialaid.iastate.edu/scholarships/), filter by category (see screenshot above), or do both. Included with the tool is a pagination function; this has become increasingly useful as the amount of entries approaches 1000. I’m still working to combine the three functions, using a mixture of front-end JavaScript and jQuery and backend PHP and MySQL. It’s a learning experience, I’ll tell you that much.

Hopefully, this tool will be available to the public…soon.

Believe