The online racing simulator
SOS - I've ran out of talent.
(10 posts, started )
#1 - Dygear
SOS - I've ran out of talent.
I pretty much ran out of talent. I've tried and tried to impalement a crew board for my Ambulance Corps, and for the life of me I just can't wrap my head around getting what we do with Marker and a White Board to translate to HTML and PHP. Here's my attempt at both Rubber Ducky Programming and getting some help should the solution not present it's self.

http://crew.wlvac.net/ Is the URL that I'm using to test away from the general members eyes. Once I'm done, it will become a page on http://www.wlvac.net/. I've programmed this too 90% of the way done, like twice now over the course of the past 2 years and I hated the implementation so much each time that I thew out all of the code.

I never got my head around how to map the actions such as "Covering a Crew", to "Going Permanent on a Crew" or "Finding Coverage For This Week" to an SQL statement, and I pretty much always hated how it looked on screen.

So maybe Rubber Ducky'ing this is not going to help. It might just be that I hate my code.
#2 - amp88
Your post and the development site you've linked to are pretty difficult to understand. Are you just venting or are you looking for input?
#3 - Dygear
Quote from amp88 :Your post and the development site you've linked to are pretty difficult to understand. Are you just venting or are you looking for input?

A bit of both.
#4 - amp88
OK...well, I can't offer any input on the 'web' side of things (HTML/PHP), but I might be able to help you with the SQL side if you can provide more details.

Can you provide details of your current database? Can you provide examples of SQL statements that you're struggling to write (e.g. assigning a crew member to a crew for a single instance/permanently or finding any crews which require members to be assigned (which I'm assuming is what you mean by "Finding Coverage For This Week"))?
#5 - Dygear
I'm using PHP 5.5.7, and SQLite 3.8.2 as the database (I updated and compiled it into PHP, that version of SQLite is non-standard for PHP 5.5.7).

The goals of the database structure is as follows
  1. Only one member can be assigned to one spot on the crew.
    Effectively, that means that a person can't fill the ALS spot and the MVO spot concurrently.
  2. A member, once taken a spot on crew owns that crew spot, be it for that week or permanently.
    There are two types that I have to deal with.
    • Permanent crew members, these are the people that have made a commitment to show up each week and fill that spot on that crew.
    • Non-Permanent crew members, that are able to fill that spot only for that week. Once the shift is over, that spot on crew becomes available once again for anyone else to pick up.
  3. A member on a crew spot can request coverage.
    Effectively, this means that they are unable to fill that spot, but they are still responsible for it until someone else states that they can take it.
Members can only sign up for spots within their level of care.
A BLS member can't sign up for an ALS spot. A non-driver can't sign up for the MVO spot. Anyone can sign up for the 5TH spot. And ALS members higher levels of care, so they can sign up for BLS spots as they wish.

So what I need is a permanent bit, and a coverage bit, right? The permanent bit would say this person is taking this spot on this crew from now on. And a coverage bit would say I need someone to fill this spot on this crew because I can't be there.

The whole SQL syntax for that is a nightmare for me. I can't wrap my head around having someone sign up for a permeate spot on say January 1st, 2014 for the Wednesday 1900 - 2300 as ALS and then need coverage for the next month for the 5th of February for that spot that they took back in January. Ideally once someone takes that spot, it would only produce one SQL data-entry. How does one handle the case of needing coverage later on for a permanent crew member.

The whole point of the database is that you also have an audit trail, so should someone pick up that spot, they then own that spot for that week that the original person needed coverage. But what if they can't do it and then they need coverage! That whole process needs to work in that situation as well. Then it comes back to the original guy who is already permeate on that spot is able to cover the guy that was covering him. I still want the audit trail, even if it's circled back to the original person who had it.

The real power in this comes from the audit reports. I want to know the number of times that a person was unable to fill there spot on crew. So when it gets to something like 3 times in a row then maybe that person should come off of that position until things settle down for them. Or people who look for coverage within 24 hours of the crew starting, how many times does that happen, is it more common with this person then that person?

Being able to build these reports is the strongest reason to use this. Instead of pen and paper.

---

If nothing else, I never properly defined what I wanted it to do. And I'm sorry, you asked from some database structure and I never gave it to you.

CREATE TABLE login(id TEXT PRIMARY KEY NOT NULL, given_name TEXT NOT NULL, family_name TEXT NOT NULL, lastseen INTEGER NOT NULL);

This table contains the information about the id of the client, and the name.

CREATE TABLE certification ( id, certification, certification_expiration, FOREIGN KEY(id) REFERENCES login(id) ON DELETE CASCADE )

This gives you information about their certification level, and when it expires. Technically speaking, it would be possible for someone who is ALS (AEMT-P, AEMT-CC) to become BLS and vice versa.

CREATE TABLE status ( id, rank, driver, class INTEGER, FOREIGN KEY(id) REFERENCES login(id) ON DELETE CASCADE )

Should someone no longer be a driver, then they can no longer take the MVO spot on crew. If they are permeate on a crew or signed up for a crew, as an MVO then they must be removed.
#6 - dawesdust_12
Mark,

May I ask why you're trying to do any logic in SQL? This seems like a situation where having a simple query and a collection of objects, and having your collection contain the logic required to do what you need.

Maybe I'm misunderstanding quite what you're asking, but breaking this down into a hierarchy of objects, as it's much easier to work with a set of objects than trying to create this mess of SQL to get the data you want. Yes the load on the DB might be a bit higher, but it's a decent trade-off for your sanity.
#7 - Dygear
I don't have an answer for that. Might be the reason I've lost my mind. I think I might need a vacation.
#8 - amp88
Note: Had to split this over 2 posts due to length.

I took a quick stab at this; very rough, and nowhere near a complete solution, but you might be able to use some of it as a base to build on.

Tables:

Users (very simple, no password (hash) field, forename/surname, date user created etc):

CREATE TABLE IF NOT EXISTS Users (
UserID INTEGER PRIMARY KEY,
Username VARCHAR(32) UNIQUE NOT NULL)

Certifications (such as 'BLS', 'ALS - AEMT-P'):

CREATE TABLE IF NOT EXISTS Certifications (
CertificationID INTEGER PRIMARY KEY,
ClassID INT NOT NULl,
Code VARCHAR(16) NOT NULL,
Description VARCHAR(32),
UNIQUE(Code, Description))

UserCertifications (keeping track of which certifications each user has and when those certifications are valid):

CREATE TABLE IF NOT EXISTS UserCertifications (
UserCertificationID INTEGER PRIMARY KEY,
UserID INTEGER NOT NULL REFERENCES Users(UserID),
CertificationID INTEGER NOT NULL REFERENCES Certifications(CertificationID),
ValidFrom DATE NOT NULL,
ValidTo DATE NOT NULL,
UNIQUE (UserID, CertificationID, ValidFrom, ValidTo))

Roles (the positions on the ambulance crew, such as 'MVO', 'CPR'):

CREATE TABLE IF NOT EXISTS Roles (
RoleID INTEGER PRIMARY KEY,
Code VARCHAR(16) UNIQUE NOT NULL)

RoleCertifications (to keep track of which certification is required for each role in the crew):

CREATE TABLE IF NOT EXISTS RoleCertifications (
RoleID INTEGER NOT NULL REFERENCES Roles(RoleID),
ClassID INTEGER NOT NULL REFERENCES Certifications(ClassID),
PRIMARY KEY(RoleID, ClassID))

Shifts (start/end date for each crew, e.g. 2014-01-04 19:00 to 2014-01-04 23:00):

CREATE TABLE IF NOT EXISTS Shifts (
ShiftID INTEGER PRIMARY KEY,
StartDate DATE NOT NULL,
EndDate DATE NOT NULL,
UNIQUE (StartDate, EndDate))

ShiftCrews (the users actually assigned to each role within a given shift). For the moment I stuck the 'Permanent' position in this table, but I think there should be a separate entity for tracking which Permanent positions each user has been assigned to, including the start/end dates for that assignment and when it was created.

CREATE TABLE IF NOT EXISTS ShiftCrews (
ShiftCrewID INTEGER PRIMARY KEY,
ShiftID INTEGER NOT NULL REFERENCES Shifts(ShiftID),
RoleID INTEGER NOT NULL REFERENCES Roles(RoleID),
UserID INTEGER NOT NULL REFERENCES Users(UserID),
Permanent BOOLEAN NOT NULL,
UNIQUE (ShiftID, UserID),
UNIQUE (ShiftID, RoleID))

CoverRequests (to store requests from permanent crew members for cover):

CREATE TABLE IF NOT EXISTS CoverRequests (
CoverRequestID INTEGER PRIMARY KEY,
ShiftCrewID INTEGER NOT NULL UNIQUE REFERENCES ShiftCrews(ShiftCrewID),
OriginalUserID INTEGER NOT NULL REFERENCES Users(UserID),
DateRequested DATE NOT NULL,
NewUserID INTEGER REFERENCES Users(UserID),
DateCoverFound DATE)

Now, populating with sample data:

Users:

INSERT INTO Users (Username) VALUES ('svettel');
INSERT INTO Users (Username) VALUES ('falonso');
INSERT INTO Users (Username) VALUES ('mwebber');
INSERT INTO Users (Username) VALUES ('lhamilton');
INSERT INTO Users (Username) VALUES ('kraikkonen');
INSERT INTO Users (Username) VALUES ('nrosberg');
INSERT INTO Users (Username) VALUES ('rgrosjean');
INSERT INTO Users (Username) VALUES ('fmassa');
INSERT INTO Users (Username) VALUES ('jbutton');
INSERT INTO Users (Username) VALUES ('nhulkenberg');
INSERT INTO Users (Username) VALUES ('sperez');
INSERT INTO Users (Username) VALUES ('pdiresta');
INSERT INTO Users (Username) VALUES ('asutil');
INSERT INTO Users (Username) VALUES ('dricciardo');
INSERT INTO Users (Username) VALUES ('jevergne');
INSERT INTO Users (Username) VALUES ('egutierrez');
INSERT INTO Users (Username) VALUES ('vbottas');
INSERT INTO Users (Username) VALUES ('pmaldonado');
INSERT INTO Users (Username) VALUES ('jbianchi');
INSERT INTO Users (Username) VALUES ('cpic');
INSERT INTO Users (Username) VALUES ('hkovalainen');
INSERT INTO Users (Username) VALUES ('gvdgarde');
INSERT INTO Users (Username) VALUES ('mchilton');

Certifications. I defined constants within my Java code for the ALS/BLS etc ClassIDs (ALS = 1, BLS = 2, MVO = 3, CPR = 4). I've added two separate ALS certifications here (one for AEMT-P and one for AEMT-CC, but as you can see they both have the same ClassID, so a User with either one is able to fill the ALS role on a ShiftCrew):

INSERT INTO Certifications (ClassID, Code, Description) VALUES (1, 'ALS', 'AEMT-P');
INSERT INTO Certifications (ClassID, Code, Description) VALUES (1, 'ALS', 'AEMT-CC');
INSERT INTO Certifications (ClassID, Code, Description) VALUES (2, 'BLS', NULL);
INSERT INTO Certifications (ClassID, Code, Description) VALUES (3, 'MVO', NULL);
INSERT INTO Certifications (ClassID, Code, Description) VALUES (4, 'CPR', NULL);

UserCertifications. I've added some Users with multiple Certifications for 2 reasons: 1) I assume this is actually possible in real life and 2) To test the code to stop a user who is already assigned to a ShiftCrew from being shown as a candidate to fill another Role on that ShiftCrew.

INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo)
SELECT UserID, CertificationID, '2013-05-20', '2014-05-20' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'svettel' AND CODE = 'ALS' AND DESCRIPTION = 'AEMT-P';
INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo)
SELECT UserID, CertificationID, '2013-06-10', '2014-06-10' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'falonso' AND CODE = 'MVO' AND DESCRIPTION IS NULL;
INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo)
SELECT UserID, CertificationID, '2013-02-20', '2014-02-20' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'mwebber' AND CODE = 'CPR' AND DESCRIPTION IS NULL;
INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo)
SELECT UserID, CertificationID, '2013-09-20', '2014-09-20' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'lhamilton' AND CODE = 'ALS' AND DESCRIPTION = 'AEMT-CC';
INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo)
SELECT UserID, CertificationID, '2013-11-15', '2014-11-15' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'nrosberg' AND CODE = 'BLS' AND DESCRIPTION IS NULL;
INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo)
SELECT UserID, CertificationID, '2013-10-15', '2014-10-15' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'nrosberg' AND CODE = 'MVO' AND DESCRIPTION IS NULL;
INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo)
SELECT UserID, CertificationID, '2013-12-12', '2014-12-12' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'kraikkonen' AND CODE = 'BLS' AND DESCRIPTION IS NULL;
INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo)
SELECT UserID, CertificationID, '2013-01-20', '2014-01-20' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'rgrosjean' AND CODE = 'ALS' AND DESCRIPTION = 'AEMT-CC';
INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo)
SELECT UserID, CertificationID, '2013-04-01', '2014-04-01' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'fmassa' AND CODE = 'MVO' AND DESCRIPTION IS NULL; " +
INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo)
SELECT UserID, CertificationID, '2013-02-01', '2014-02-01' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'fmassa' AND CODE = 'BLS' AND DESCRIPTION IS NULL;
INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo)
SELECT UserID, CertificationID, '2013-07-20', '2014-07-20' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'jbutton' AND CODE = 'MVO' AND DESCRIPTION IS NULL;
INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo)
SELECT UserID, CertificationID, '2013-06-15', '2014-06-15' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'nhulkenberg' AND CODE = 'CPR' AND DESCRIPTION IS NULL;
INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo)
SELECT UserID, CertificationID, '2013-08-20', '2014-08-20' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'sperez' AND CODE = 'BLS' AND DESCRIPTION IS NULL;
INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo)
SELECT UserID, CertificationID, '2013-10-01', '2014-10-01' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'pdiresta' AND CODE = 'BLS' AND DESCRIPTION IS NULL;
INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo)
SELECT UserID, CertificationID, '2013-06-01', '2014-06-01' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'pdiresta' AND CODE = 'MVO' AND DESCRIPTION IS NULL;
INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo)
SELECT UserID, CertificationID, '2013-01-10', '2014-01-10' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'asutil' AND CODE = 'MVO' AND DESCRIPTION IS NULL;
INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo)
SELECT UserID, CertificationID, '2013-02-28', '2014-02-28' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'dricciardo' AND CODE = 'MVO' AND DESCRIPTION IS NULL;
INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo)
SELECT UserID, CertificationID, '2013-03-01', '2014-03-01' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'jevergne' AND CODE = 'CPR' AND DESCRIPTION IS NULL;
INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo)
SELECT UserID, CertificationID, '2013-03-01', '2014-03-01' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'jevergne' AND CODE = 'BLS' AND DESCRIPTION IS NULL;
INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo)
SELECT UserID, CertificationID, '2013-07-20', '2014-07-20' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'egutierrez' AND CODE = 'MVO' AND DESCRIPTION IS NULL;
INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo)
SELECT UserID, CertificationID, '2013-02-20', '2014-02-20' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'vbottas' AND CODE = 'ALS' AND DESCRIPTION = 'AEMT-P';
INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo)
SELECT UserID, CertificationID, '2013-11-03', '2014-11-03' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'pmaldonado' AND CODE = 'BLS' AND DESCRIPTION IS NULL;
INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo)
SELECT UserID, CertificationID, '2013-06-20', '2014-06-20' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'jbianchi' AND CODE = 'CPR' AND DESCRIPTION IS NULL;
INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo)
SELECT UserID, CertificationID, '2013-04-30', '2014-04-30' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'cpic' AND CODE = 'MVO' AND DESCRIPTION IS NULL;
INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo)
SELECT UserID, CertificationID, '2013-02-20', '2014-02-20' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'hkovalainen' AND CODE = 'MVO' AND DESCRIPTION IS NULL;
INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo) " +
SELECT UserID, CertificationID, '2013-01-10', '2014-01-10' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'gvdgarde' AND CODE = 'CPR' AND DESCRIPTION IS NULL;
INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo)
SELECT UserID, CertificationID, '2013-01-10', '2014-01-10' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'gvdgarde' AND CODE = 'MVO' AND DESCRIPTION IS NULL;
INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo)
SELECT UserID, CertificationID, '2013-04-19', '2014-04-19' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'mchilton' AND CODE = 'MVO' AND DESCRIPTION IS NULL;

Roles:

INSERT INTO Roles (Code) VALUES ('ALS');
INSERT INTO Roles (Code) VALUES ('BLS');
INSERT INTO Roles (Code) VALUES ('MVO');
INSERT INTO Roles (Code) VALUES ('CPR');
INSERT INTO Roles (Code) VALUES ('5TH');

RoleCertifications. Note that the 'BLS' role has both the BLS and ALS ClassIDs and the '5TH' role has the ALS/BLS/MVO and CPR ClassIDs, since it can be filled by anyone. Also, I'm again using the ClassID constants defined above (ALS = 1, BLS = 2, MVO = 3, CPR = 4).

INSERT INTO RoleCertifications (RoleID, ClassID) SELECT ROLEID, 1 FROM ROLES WHERE CODE = 'ALS';
INSERT INTO RoleCertifications (RoleID, ClassID) SELECT ROLEID, 2 FROM ROLES WHERE CODE = 'BLS';
INSERT INTO RoleCertifications (RoleID, ClassID) SELECT ROLEID, 1 FROM ROLES WHERE CODE = 'BLS';
INSERT INTO RoleCertifications (RoleID, ClassID) SELECT ROLEID, 3 FROM ROLES WHERE CODE = 'MVO';
INSERT INTO RoleCertifications (RoleID, ClassID) SELECT ROLEID, 4 FROM ROLES WHERE CODE = 'CPR';
INSERT INTO RoleCertifications (RoleID, ClassID) SELECT ROLEID, 1 FROM ROLES WHERE CODE = '5TH';
INSERT INTO RoleCertifications (RoleID, ClassID) SELECT ROLEID, 2 FROM ROLES WHERE CODE = '5TH';
INSERT INTO RoleCertifications (RoleID, ClassID) SELECT ROLEID, 3 FROM ROLES WHERE CODE = '5TH';
INSERT INTO RoleCertifications (RoleID, ClassID) SELECT ROLEID, 4 FROM ROLES WHERE CODE = '5TH';

Shifts. I wrote a quick bit of Java to populate the Shifts table. I defined a start and end date (the 3rd of February and 9th of February 2014 respectively) and I went through from the 3rd to the 9th adding shifts based on the day of the week. For weekdays I added 2 shifts per day (19:00-23:00 and 23:00-07:00 the next morning). For weekends I added 4 shifts per day (07:00-13:00, 13:00-19:00, 19:00-23:00 and 23:00-07:00 the next morning). This is obviously a naive implementation (it doesn't respect holidays or special shifts), but obviously it was done for simplicity.

INSERT INTO Shifts (StartDate, EndDate) VALUES ('2014-02-03 23:00', '2014-02-04 07:00');
INSERT INTO Shifts (StartDate, EndDate) VALUES ('2014-02-04 19:00', '2014-02-04 23:00');
INSERT INTO Shifts (StartDate, EndDate) VALUES ('2014-02-04 23:00', '2014-02-05 07:00');
INSERT INTO Shifts (StartDate, EndDate) VALUES ('2014-02-05 19:00', '2014-02-05 23:00');
INSERT INTO Shifts (StartDate, EndDate) VALUES ('2014-02-05 23:00', '2014-02-06 07:00');
INSERT INTO Shifts (StartDate, EndDate) VALUES ('2014-02-06 19:00', '2014-02-06 23:00');
INSERT INTO Shifts (StartDate, EndDate) VALUES ('2014-02-06 23:00', '2014-02-07 07:00');
INSERT INTO Shifts (StartDate, EndDate) VALUES ('2014-02-07 19:00', '2014-02-07 23:00');
INSERT INTO Shifts (StartDate, EndDate) VALUES ('2014-02-07 23:00', '2014-02-08 07:00');
INSERT INTO Shifts (StartDate, EndDate) VALUES ('2014-02-08 07:00', '2014-02-08 13:00');
INSERT INTO Shifts (StartDate, EndDate) VALUES ('2014-02-08 13:00', '2014-02-08 19:00');
INSERT INTO Shifts (StartDate, EndDate) VALUES ('2014-02-08 19:00', '2014-02-08 23:00');
INSERT INTO Shifts (StartDate, EndDate) VALUES ('2014-02-08 23:00', '2014-02-09 07:00');
INSERT INTO Shifts (StartDate, EndDate) VALUES ('2014-02-09 07:00', '2014-02-09 13:00');
INSERT INTO Shifts (StartDate, EndDate) VALUES ('2014-02-09 13:00', '2014-02-09 19:00');
INSERT INTO Shifts (StartDate, EndDate) VALUES ('2014-02-09 19:00', '2014-02-09 23:00');
INSERT INTO Shifts (StartDate, EndDate) VALUES ('2014-02-09 23:00', '2014-02-10 07:00');

ShiftCrews. This is actually assigning Users to particular Roles within Shifts. Again, going back to the mention of the Permanent bit above, this isn't a very good solution, but I just wanted to get some ideas down. To populate this I wrote a simple bit of Java code again for the permanent positions. Basically I gave certain users Shifts (such as weekdays 19:00-23:00) and just went from the StartDate to the EndDate (the 3rd Feb - 9th Feb 2014 adding them to the ShiftCrews on the correct days. If you were going to do this in the real environment you would probably be doing a longer period (such as 6-12 months or whatever), so you'd probably want to do until the end of that period or until their certification for the role expired. Those implementation details are a bit beyond the scope of this post, though.

A couple of non-permanent assignments:

INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent)
SELECT SHIFTID, ROLEID, USERID, 0 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-03 19:00' AND CODE = '5TH' AND USERNAME = 'nrosberg';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent)
SELECT SHIFTID, ROLEID, USERID, 0 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-03 19:00' AND CODE = 'ALS' AND USERNAME = 'lhamilton';

Permanent assignments:

INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-03 23:00' AND CODE = 'MVO' AND USERNAME = 'egutierrez';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-03 23:00' AND CODE = 'CPR' AND USERNAME = 'jbianchi';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-03 23:00' AND CODE = 'ALS' AND USERNAME = 'vbottas';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-03 23:00' AND CODE = 'BLS' AND USERNAME = 'pdiresta';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-03 23:00' AND CODE = '5TH' AND USERNAME = 'sperez';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-03 19:00' AND CODE = 'CPR' AND USERNAME = 'gvdgarde';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-03 19:00' AND CODE = 'MVO' AND USERNAME = 'mchilton';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-04 23:00' AND CODE = 'MVO' AND USERNAME = 'egutierrez';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-04 23:00' AND CODE = 'CPR' AND USERNAME = 'jbianchi';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-04 23:00' AND CODE = 'ALS' AND USERNAME = 'vbottas';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-04 23:00' AND CODE = 'BLS' AND USERNAME = 'pdiresta';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-04 23:00' AND CODE = '5TH' AND USERNAME = 'sperez';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-04 19:00' AND CODE = 'CPR' AND USERNAME = 'gvdgarde';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-04 19:00' AND CODE = 'MVO' AND USERNAME = 'mchilton';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-05 23:00' AND CODE = 'MVO' AND USERNAME = 'egutierrez';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-05 23:00' AND CODE = 'CPR' AND USERNAME = 'jbianchi';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-05 23:00' AND CODE = 'ALS' AND USERNAME = 'vbottas';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-05 23:00' AND CODE = 'BLS' AND USERNAME = 'pdiresta';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-05 23:00' AND CODE = '5TH' AND USERNAME = 'sperez';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-05 19:00' AND CODE = 'CPR' AND USERNAME = 'gvdgarde';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-05 19:00' AND CODE = 'MVO' AND USERNAME = 'mchilton';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-06 23:00' AND CODE = 'MVO' AND USERNAME = 'egutierrez';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-06 23:00' AND CODE = 'CPR' AND USERNAME = 'jbianchi';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-06 23:00' AND CODE = 'ALS' AND USERNAME = 'vbottas';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-06 23:00' AND CODE = 'BLS' AND USERNAME = 'pdiresta';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-06 23:00' AND CODE = '5TH' AND USERNAME = 'sperez';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-06 19:00' AND CODE = 'CPR' AND USERNAME = 'gvdgarde';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-06 19:00' AND CODE = 'MVO' AND USERNAME = 'mchilton';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-07 23:00' AND CODE = 'MVO' AND USERNAME = 'egutierrez';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-07 23:00' AND CODE = 'CPR' AND USERNAME = 'jbianchi';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-07 23:00' AND CODE = 'ALS' AND USERNAME = 'vbottas';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-07 23:00' AND CODE = 'BLS' AND USERNAME = 'pdiresta';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-07 23:00' AND CODE = '5TH' AND USERNAME = 'sperez';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-07 19:00' AND CODE = 'CPR' AND USERNAME = 'gvdgarde';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-07 19:00' AND CODE = 'MVO' AND USERNAME = 'mchilton';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-08 23:00' AND CODE = 'MVO' AND USERNAME = 'egutierrez';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-08 23:00' AND CODE = 'CPR' AND USERNAME = 'jbianchi';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-08 23:00' AND CODE = 'ALS' AND USERNAME = 'vbottas';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-08 23:00' AND CODE = 'BLS' AND USERNAME = 'pdiresta';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-08 23:00' AND CODE = '5TH' AND USERNAME = 'sperez';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-08 07:00' AND CODE = 'BLS' AND USERNAME = 'pmaldonado';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-08 07:00' AND CODE = 'MVO' AND USERNAME = 'hkovalainen';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-08 07:00' AND CODE = 'CPR' AND USERNAME = 'jevergne';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-09 23:00' AND CODE = 'MVO' AND USERNAME = 'egutierrez';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-09 23:00' AND CODE = 'CPR' AND USERNAME = 'jbianchi';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-09 23:00' AND CODE = 'ALS' AND USERNAME = 'vbottas';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-09 23:00' AND CODE = 'BLS' AND USERNAME = 'pdiresta';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-09 23:00' AND CODE = '5TH' AND USERNAME = 'sperez';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-09 07:00' AND CODE = 'BLS' AND USERNAME = 'pmaldonado';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-09 07:00' AND CODE = 'MVO' AND USERNAME = 'hkovalainen';
INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-09 07:00' AND CODE = 'CPR' AND USERNAME = 'jevergne';

An unfulfilled CoverRequest. If the CoverRequest is fulfilled you would populate the NewUserID and DateCoverFound fields then modify the appropriate row in the ShiftCrew table to change the Permanent value from TRUE to FALSE (1 to 0) and update the UserID to the NewUserID value.

INSERT INTO CoverRequests (ShiftCrewID, OriginalUserID, DateRequested) SELECT ShiftCrewID, U.UserID, '2014-01-04' FROM SHIFTCREWS SC JOIN SHIFTS S ON SC.SHIFTID = S.SHIFTID JOIN ROLES R ON SC.ROLEID = R.ROLEID JOIN USERS U WHERE STARTDATE = '2014-02-06 19:00' AND R.CODE = 'MVO' AND USERNAME = 'mchilton';

#9 - amp88
OK, so now a few queries to try and get some useful information.

To view all Roles on all Shifts. Roles which currently have Users assigned will also display the Username of the assigned User. Roles which currently need users to fill them will be displayed with NULL details for the Username:

SELECT S.SHIFTID, R.ROLEID, STARTDATE, ENDDATE, CODE, USERNAME
FROM SHIFTS S
JOIN ROLES R
LEFT OUTER JOIN SHIFTCREWS SC ON S.SHIFTID = SC.SHIFTID AND R.ROLEID = SC.ROLEID
LEFT OUTER JOIN USERS U ON SC.USERID = U.USERID
ORDER BY S.STARTDATE

To view which Shifts need Users to fill Roles:

SELECT S.SHIFTID, R.ROLEID, STARTDATE, ENDDATE, CODE
FROM SHIFTS S
JOIN ROLES R
LEFT OUTER JOIN SHIFTCREWS SC ON S.SHIFTID = SC.SHIFTID AND R.ROLEID = SC.ROLEID
WHERE SC.SHIFTID IS NULL
ORDER BY S.STARTDATE

To view Users who could potentially fill a Role on a ShiftCrew which needs someone. This is a very simple approach, so it only looks for Users who have the required certification for the Role (and it's valid at the time of the Shift) who aren't already on the ShiftCrew for this shift only. That is, this query will display Users who are on the ShiftCrew for the previous or following shift. As Dustin points out above, the logic for finding Users capable of covering slots on a ShiftCrew could get quite complicated pretty quickly, so perhaps this is something better done either by a human supervisor or with a more complex algorithm in PHP. There are plenty of potential problems with finding a replacement User. For instance, say you're looking for an MVO to fill a crew. One of your existing crew members is currently in the CPR role but she's qualified for both CPR and MVO. The only Users who are available for the Shift are only qualified in CPR. The obvious solution is to move the woman who's currently in the CPR role into the MVO role then bring in a replacement for the CPR role. However, to code that into SQL along with other rules/laws (restrictions on number of hours worked per week / in a row etc) would get very messy fast.

In the query below I've manually set the ShiftID and RoleID to 1 and 2, respectively. The ShiftID value of 1 is found twice in the code (once within the bracketed section for the EXISTINGCREW and once in the WHERE clause of the overall statement. The RoleID value of 2 is found once in the code (in the WHERE clause of the overall statement). These values for ShiftID and RoleID are determined using the above query ("To view which Shifts need Users to fill Roles").

SELECT S.SHIFTID, R.ROLEID, S.STARTDATE, S.ENDDATE, R.CODE, USERNAME
FROM SHIFTS S
JOIN ROLES R
JOIN ROLECERTIFICATIONS RC ON R.ROLEID = RC.ROLEID
JOIN CERTIFICATIONS C ON RC.CLASSID = C.CLASSID
JOIN USERCERTIFICATIONS UC ON C.CERTIFICATIONID = UC.CERTIFICATIONID
JOIN USERS U ON UC.USERID = U.USERID
LEFT OUTER JOIN (
SELECT USERID
FROM SHIFTCREWS SC
WHERE SC.SHIFTID = 1
) EXISTINGCREW ON UC.USERID = EXISTINGCREW.USERID
WHERE SHIFTID = 1 AND R.ROLEID = 2 AND UC.VALIDFROM <= S.STARTDATE AND UC.VALIDTO >= S.ENDDATE AND EXISTINGCREW.USERID IS NULL

The above query can be adapted to find Users for unfulfilled CoverRequests too, with the same caveats as above on filtering.

Anyway, a quick effort that you may be able to take some inspiration from or use as a base to grow. Feel free to ask any questions.

In case it's of any use, I've attached my Java code I wrote for this to this post (renamed as .txt for attachment filter).
Attached files
AmbulanceCrewTestMain.txt - 27.3 KB - 202 views
#10 - Dygear
Thank you for your help amp88. There are some changes that I'd like to see, such as not having a shifts table, I can move the context of a shift into the PHP class. I'd also move the RoleCertifications as a check to the class as well, as I keep this information in their session data, that I access via a member class. Although, I might take your schema as I think it's better then the way I handled it. ShiftCrews is interesting, and I think I can build on that into the PHP class as well, within the context of adding a member to a crew.

Thank you so much for you help, I'll let you know how I end'ed up programming this up later on once it's all done.

SOS - I've ran out of talent.
(10 posts, started )
FGED GREDG RDFGDR GSFDG