Optimising Database Structure

I'm developing a reward system for our VLE which uses three separate technologies - JavaScript for most of the client-side/display processing, PHP to communicate with the database and MySQL for the database itself.

I've attached three screenshots of my "transactions" table. Its structure, a few example records and an overview of its details.

The premise is that members of staff award points to students for good behaviour etc. This can mean that classes of 30 students are given points at a single time. Staff have a limit of 300 points/week and there are around 85 staff currently accessing the system (this may rise).

The way I'm doing it at the moment, every "transaction" has a "Giver_ID" (the member of staff awarding points), a "Recipient_ID" (the student receiving the points), a category and a reason. This way, every time a member of staff issues 30 points, I'm putting 30 rows into the database.

This seemed to work early on, but within three weeks I already have over 12,000 transactions in the database.

At this point it gets a bit more complicated. On the Assign Points page (another screenshot attached), when a teacher clicks into one of their classes or searches for an individual student, I want the students' points to be displayed. The only way I can currently do this on my system is to do a "SELECT * FROM 'transactions'" and put all the information into an array using the following JS:

var Points = { "Recipient_ID" : "0", "Points" : "0" };

function getPoints (data) {
    for (var i = 0; i < data.length; i++) {
        if (Points[data[i].Recipient_ID]) {
            Points[data[i].Recipient_ID] = parseInt(Points[data[i].Recipient_ID]) + parseInt(data[i].Points);
        } else {
            Points[data[i].Recipient_ID] = data[i].Points;
        }
    }
}

When logging in to the system internally, this appears to work quickly enough. When logging in externally however, this process takes around 20 seconds, and thus doesn't display the students' points values until you've clicked/searched a few times.

I'm using the following code in my PHP to access these transactions:

function getTotalPoints() {
    $sql = "SELECT * 
        FROM `transactions`";

    $res = mysql_query($sql);
    $rows = array(); 
    while($r = mysql_fetch_assoc($res)) {
        $rows[] = $r;
    }

    if ($rows) {
        return $rows;
    } else {
        $err = Array("err_id" => 1);
        return $err;
    }
}

So, my question is, how should I actually be approaching this? Full-text indices; maybe a student table with their total points values which gets updated every time a transaction is entered; mass-transactions (i.e. more than one student receiving the same points for the same category) grouped into开发者_高级运维 a single database row? These are all things I've contemplated but I'd love someone with more DB knowledge than myself to provide enlightenment.

Example records

Table structure

Table overview

Assign Points interface

Many thanks in advance.

Your problem is your query:

SELECT * FROM `transactions`

As your data set gets bigger, this will take longer to load and require more memory to store it. Rather determine what data you need specifically. If it's for a particular user:

SELECT SUM(points) FROM `transactions` WHERE Recipient_ID=[x]

Or if you want all the sums for all your students:

SELECT Recipient_ID, SUM(points) AS Total_Points FROM `transactions` GROUP BY Recipient_ID;

To speed up selections on a particular field you can add an index for that field. This will speed up the selections, especially as the table grows.

ALTER TABLE `transactions` ADD INDEX Recipient_ID (Recipient_ID);

Or if you want to display a paginated list of all the entries in transactions:

SELECT * FROM `transactions` LIMIT [page*num_records_per_page],[num_records_per_page];

e.g.: SELECT * FROM `transactions` LIMIT 0,25 ORDER BY Datetime; # First 25 records

Adding to Tom's suggestion, you may want to consider normalizing your database further. I assume you now have 3 tables:

students (id, name, ...)

staff (id, name, ...)

transactions (id, student_id, staff_id, points, date, reason)

A more normalized form uses more tables with less data:

students (id, name, ...)

staff (id, name, ...)

transactions (id, staff_id, points, date, reason)

transactions_students (transaction_id, student_id)

Adding a transaction then becomes a two-step process: First you create one transaction record, and then you insert multiple records into transactions_students, each one linking the transaction to one student. Note that you can create a view that behaves exactly like the original denormalized table for selecting, something like:

CREATE VIEW vw_transactions AS SELECT transactions.*, transactions_students.student_id FROM transactions INNER JOIN transactions_students WHERE transactions_students.transaction_id = transactions.id

This will drastically reduce the number of records in the transactions table, and it avoids storing the date and the reason redunantly. The downside is that linking transactions to students requires one extra join - but if you have your foreign keys and indexes set up properly, this doesn't have to be a problem at all.

I'd index the Recipient_ID so you can search for 1 person specifically at any given point or at least be able to GROUP your data more effectively. If you do opt to group by category_id then i'd add a seperate or combined index to category_id too.

The second suggestion would be to GROUP and AGGREGATE your data on the fly. For example:

SELECT Recipient_ID, Category_ID, SUM(points) FROM transactions GROUP BY Recipient_ID, Category_ID

These two suggestions should dramatically upgrade your performance because instead of calculating the total points for your students on the PHP/JS side, you will do it directly on the database.