Introduction
I am creating an e-learning website that allows people to buy Education Courses for other people (college friends etc…). Receivers of these Courses have to sign into the website to complete one tutorial every day for the duration of the course… they cannot complete more than one tutorial each day. If receivers do not complete a tutorial each day, then they immediately fail the course, however the buyer can assign a number of lifelines to the user so they can miss a number of days during the duration of the course.
Receivers will need to choose their timezone so that the site will know the start and end of each specific users day.
Help Required
I need some way of automating the courses and tutorials, to check if tutorials have been completed the previous day and to set up tutorials for the current day… so i have decided that the best way (IMO) is to create a cron script that will run at the very start of each users day.
Considering each user will be from a different timezone, the cron will need to run every hour, on the hour (24 times a day at ##:00:01) however each time it runs, it will only need to query the courses who’s day starts on the current hour that the script is running (current server hour)
So considering the cron will be running at the very start of each users day, it can check the status of yesterdays tutorial and prepare the tutorial for the current day…
I have tried to illustrate this in the image below… do you think this will work effectively… thanks in advance for your help…
I would not use start_date, end_date and duration; you only need two of those. The most obvious would be to just use start_date and duration. You can calculate end_date from that. The problem with storing all three is that you can get update anomalies. For example if you increase the duration but not the end_date the record doesn’t make sense any more.
Don’t use both timezone and start_hour, just use timezone. Same reasoning as (1).
I would combine the last bit in one query
UPDATE courses SET today_status='incomplete' WHERE status='active' AND something_with_timezone_here
Don’t use SELECT * , only select the fields you need
Subtract -1 is the same as “add 1”, I don’t think that’s what you were going for
Hi, thanks for your reply… i was only using pseudocode to try illustrate what i was trying to achieve…
The reason i used start_date, end_date and duration was because i thought it would be best to calculate the end date at the start so that the site would not have do carry out the calculation each time it needed to find the end_date… the same goes for timezone and start_hour
Is my above reasoning incorrect or bad practice… i originally had what you said but i thought i might be best to add additional fields with the calculations already worked out…
Sure you can do that, and your reasoning makes sense, but you need to be hyper aware of these dependencies within the table. You could even go so far as to add triggers to the database to deny updates that would be incorrect. Certainly something to think about.
It’s one of those times where you can break/ignore the rules to get better performance, but like I said, you need to be very aware that you are doing this.
It would also help to indicate for yourself which fields are “master” fields, and which are “slave” (or rather “derived”) fields. So for example the start_hour is derived from the timezone, so you will always do that and never the other way around (i.e. don’t derive a timezone from start_hour).
