MySQL Question About Scheduling

MySQL Question about scheduling

Scheduling background jobs is not part of the SQL command set. So it is down to each database vendor to provide their own extension. In Oracle it is a PL/SQL package, DBMS_SCHEDULER (or DBMS_JOB in older versions). In MYSQL it would appear to be the Event Scheduler, which use SQL syntax.

Edit

As Paul Dixon has pointed out the Event Scheduler is a relatively recent extension to MySQL (I'm not a MySQL expert, so forgive if this is not helpful to you).

LAMP: How to Implement Scheduling?

Or, rather than doing any, you know, real work, simply create an interface for the users, and then publish entries in cron! Rather than having cron call you every minute, have it call scripts as directed by the users. When they add or change jobs, rewrite the crontab.

No big deal.

In unix, cron allows each user (unix login that is) to have their own crontab, so you can have one dedicated to your app, don't have to use the root crontab for this.

Which MySQL data type to use for scheduling?

Use DATETIME or INT

I prefer to store date&time in unix timestamp (INT), because i can use it in PHP functions like date

echo "Movie will start at ".date("H:i", $row['start_time']);

And it's easy to manipulate with it:

echo "Movie will start in ".(time() - $row['start_time'])." seconds";

Current date and time: http://www.unixtimestamp.com/

If you want to copy movies to next day:

 $sql = "SELECT * FROM movies WHERE start_time >= ".strtotime("today 00:00")." AND start_time <= ".strtotime("today 23:59");

 // query..
 foreach($results as $row) {
    $new_start = strtotime("+1 day", $row['start_time'];    
    // insert
 }

Schedule MySQL query every 24 hours; query table and store results in another table

You can use the mysql event scheduler for this. Here's an example with your current query inserting data into a table called "new_table", starting at 3 AM on March 28.

DELIMITER $$

CREATE 
    EVENT `daily_backup` 
    ON SCHEDULE EVERY 1 DAY STARTS '2015-03-28 03:00:00' 
    DO BEGIN

        INSERT INTO new_table (ItemID, Price, Time) 
            SELECT ItemID, Price, NOW()
            FROM table
            WHERE ItemID = %d
            GROUP BY Price
            ORDER BY COUNT(*) DESC LIMIT 1;

    END */$$

You can do the same thing with your other queries; they can be put into the same event, before the END

MySQL Daily Appointment Scheduling Schema

Before I go into how I believe you should structure your Provider 'Schedule' Table, please make sure to, in the future, remove fluff.

More on fluff here.

It may serve you better to make the following changes:

• make all column headers lowercase, as this might prevent errors if you attempt to query your database another way
• change scheduleId to id
• Instead of having seven columns, one for every day of the week, you could simply put a weekDay column that stores the value of that weekday
• Then create columns for startTime, endTime, lunchTime and lunchLength
• Finally, create a scheduleId column that ties together all the different weekday rows of someone's schedule to one provider

Some considerations:

• Instead of having strings "Monday" or "Sunday" in the weekDay column you could instead insert 0..6, where 0 is a Sunday and 6 is a Saturday to make it more compatible with other languages
• You could always just keep scheduleId in this table and create another table with the individual schedule days and link them with a foreign key, but this might prove to cause more problems than it's worth
• Keeping that lunchLength as just an integer, as that will make everything easier

The reasoning behind splitting up the data as much as possible is because if you are querying using another language you might need to go through all the extra work of splitting those Monday and Tuesday columns if you just want the startTime for instance.

Hopefully the above is either a solution or allows you to consider another approach.

Database for monthly schedule and appointments

By briefly looking at your schema, it looks good.

A "schedule" is just the "appointment" table, but you will need to add a start time and an end time to it.

A SELECT on that table WHERE doctor_id = X AND start_time >= first-day-of-the-month AND end_time < first-day-of-next-month gives you doctor X's schedule.

EDIT

For determining whether the user is an admin, patient or doctor, you have a number of options.

• Option #1: introduce a "user type" with possible values: "ADMIN", "PATIENT", "DOCTOR".

• Option #2: use a query to determine whether the user_id of the user can be found in the admin, patient, or doctor tables.

• Option #3: (my favorite) add three foreign keys to the user table: admin_id, patient_id, and doctor_id, only one of which may be non-null.

The advantage of the last approach is that it maintains the relational nature of your schema: the user row is related to the (admin, patient or doctor) row, and the database can enforce referential integrity, meaning that if the patient_id field is non-null, then the database will make sure that the value of this field does in fact correspond to one of the user_id values in the patient table.

---

Related Topics