I need to keep track of the progress of my users through courses on a site I'm working on. THere are 25 courses at the moment, each with anywhere from 3 to 20 classes and each class with 3 - 8 tasks associated with it. I'm wondering out to store this in the database.
For each course I need to store three pieces of data - nid, class, and progress #. At the moment, I have it set up like this:
One table with the fields being uid as the primary id and then each course as a seperate field that stores serialized objects with the nid, class, and progress #.
My question is this: There might be 50 people taking each course at any time, updating the serialized objects around once every 5 minutes. Is this the best way to store the data?
Also - right now it's only indexed by UID. My worry is that we may need to gather information on how many people are on what task in what course. This would be impossible with the serialized data, so I may need to either set up that table differently or create a new table with the courses/classes/task and the number of people on that task.
What is the advice of the great Drupal gurus?
Comments
I would use a table with uid,
I would use a table with uid, nid, class and progress as separate fields/columns. For one thing it makes it easier to pull information out on a given user (uid) for a given course (nid) and even class. Since it avoids needing to serialize/unserialize fields it should also be slightly faster.
I would use a primary index on uid, nid, class (since that should be unique) and additional indexes on uid, nid and class. Or if class is not unique across courses instead of class one on nid and class.
Thanks, nevets. My fear with
Thanks, nevets.
My fear with that approach is that each user can potentially be enrolled in many courses at the same time, and each course needs its specific class and task data, so potentially we would either have over 1000 columns in that table. Alternatively, I can set up a separate table for each course with UID, nid (each specific task has a particular node associated with it so although we technically don't need this since we can find it by looking it up based on the course/class/task, it makes it a little easier to keep that), class, and task.
Quick question - can I use Drupal's %s for the table name? For example:
If that's the case than I think one table per course may be a good idea.
You should be able to use
You should be able to use only table for all courses with a small number of columns.
The table would have a set of columns that uniquely identify the row, that seems to me to be uid (user),course, and class. Any other columns like progress would be tracking data for that user/course/class.