It's a date!
-
Scheduling system. Employee availability has a start datetime, an end datetime, and a "day of week" integer. If day of week is set, this is an ongoing schedule-- ie, your schedule for every Monday. If day of week is null, then it's a specific override for a specific day, and the date portion of start datetime comes into play.
There's also a flag for "available" or "unavailable".
Simple so far. Then the question appears: how can you tell the difference between someone who has NO schedule for a specific date, versus someone who had a schedule, but put in an override marking themselves unavailable.
Oh, that's easy. Insert a second record for the employee with the same time range as their Ongoing schedule, but marked Unavailable. Then it's a simple select-- if they have no entries for that Day Of Week, and they have no overrides for that date, they don't have a schedule. It's a blank. If they do have entries for that Day Of Week, but have overrides covering that timeframe with Unavailable, they aren't available. It's a big red square.
Or, you can do it the way the system's now been designed by a moss-with-a-B:
1) create a second table called "Dates" with an integer primary key, and the employee ID as a foreign key, and a datetime field
2) Add a nullable foreign key reference to the schedule table to the Dates primary key.
3) Whenever a special override is saved, check the Dates table to see if the employee has an entry for that date. If not, insert a new Dates record.
4) Take the primary key for the Dates records, and put it into the Schedule table for that override entry.
5) If, instead, someone wants to make themselves unavailable for the entire day (ie: override their ongoing schedule with unavailable), insert a record into the Dates table, but nothing into the Schedule table. There is, of course, now no reference from Schedule to Dates, except by way of Employee Number
6) Now when checking for availability for a specific day, look at their ongoing schedule. Then make a join to the Dates table on that nullable foreign key-- somehow. And somehow, if the employee has an entry for that date, but no entries in the Schedule, then they are unavailable for the whole day. But if they do have both entries in the Dates table and the Schedule table, then use that to determine their availability, completely ignoring the Ongoing entries.
I'm writing this out for two reasons. One is so I might actually even begin to understand this goddamn fucking stupid idea (which I still don't). The second is to inflict it on all of you, to lessen my pain.
For reference, here's the old data model:
[IMG]http://i.imgur.com/11shR.png[/IMG]
Here's the new "simpler, more logical" one:
[IMG]http://i.imgur.com/BpkqW.png[/IMG]
-
Normalisation, you can't explain that.
-
@zipfruder said:
Normalisation, you can't explain that.
I can explain it by example. For example, this? This isn't it.
There's no repeating data being factored out. Sure, the date repeats, but it is the ONLY piece of information that repeats. And the date directly describes an employees Schedule Preference, hence it should remain in its original table. Furthermore, we're now repeating the Employee No column in two tables for no reason.
Might as well just go full EAV at this point. :|
-
It's a basic problem that SQL wasn't built to solve: "Put your hand up if you're not here." A related problem is "Put your hand up if your brother's not here."
How is the table of employee-dates generated? The guru runs a script once a year to create next year's dates? Great fun when the guru leaves and a true WTF.
None of which changes the fact that the business requirement (find people without a schedule) is perfectly reasonable and not a WTF.
If you can use subqueries then something like:
SELECT (fields)
FROM employees
WHERE emp_id NOT IN (SELECT emp_id FROM schedules WHERE (complex date-selection expression) )can work. But you can't always use this type of subquery, either due to the back-end database or the front-end reporting tool not supporting it.
-
@Qwerty said:
It's a basic problem that SQL wasn't built to solve: "Put your hand up if you're not here."
NOT IN
-
@blakeyrat said:
@Qwerty said:
It's a basic problem that SQL wasn't built to solve: "Put your hand up if you're not here."
NOT IN
That's "Put up your hand if you're not Sally or Peter".
-
@pkmnfrk said:
@blakeyrat said:
@Qwerty said:
It's a basic problem that SQL wasn't built to solve: "Put your hand up if you're not here."
NOT IN
That's "Put up your hand if you're not Sally or Peter".
Oh, well you can't say "put up your hand if you're not here" because that doesn't make sense. "Put up your hand if you're not -52" {infinite results} "Put up your hand if your name's not Bob" {infinite results}
-
Since blakey got the first one, I've got this one:
@Qwerty said:
A related problem is "Put your hand up if your brother's not here."
LEFT/RIGHT JOIN.
-
Expressing schedules in SQL is a colossal fuckpain anyway. My kingdom for a DATETIMERANGE, DATETIMESPAN datatypes and operators liike the MSSQL geometric operators.
-
Agreed - temporal data is a pain in a RDBMS. It seems like this is a good topic for a book. Maybe a SQL guru might write such a book.
-
@Weng said:
Oracle's BETWEEN operator?Expressing schedules in SQL is a colossal fuckpain anyway. My kingdom for a DATETIMERANGE, DATETIMESPAN datatypes and operators liike the MSSQL geometric operators.
-
@Severity One said:
@Weng said:
Oracle's BETWEEN operator?Expressing schedules in SQL is a colossal fuckpain anyway. My kingdom for a DATETIMERANGE, DATETIMESPAN datatypes and operators liike the MSSQL geometric operators.
a BETWEEN b AND c just compiles out to a >= b AND a <= c
What Weng wants is something like this, to see which employees are available in a specific time range:
SELECT distinct employee_id
FROM employee_availability as ea
WHERE [b]DATETIMERANGE(e.start_dt, e.end_dt, @start_dt, @end_dt)[/b]Otherwise, you have to manually expand it to check for overlaps
WHERE
(e.start_dt between @start_dt AND @end_dt)
OR
(e.end_dt between @start_dt AND @end_dt)
OR
(@start_dt between e.start_dt AND e.end_dt)
OR
(@end_dt between e.start_dt AND e.end_dt)
-
@Lorne Kates said:
Otherwise, you have to manually expand it to check for overlaps
Isn't this where custom functions and stored procedures rear their head?
-
@Cassidy said:
@Lorne Kates said:
Otherwise, you have to manually expand it to check for overlaps
Isn't this where custom functions and stored procedures rear their head?Do you honestly think it's a bad idea for a database system to have a "timespan" data type, or are you just being a contrarian?
-
@Qwerty said:
It's a basic problem that SQL wasn't built to solve: "Put your hand up if you're not here." A related problem is "Put your hand up if your brother's not here."
How is the table of employee-dates generated? The guru runs a script once a year to create next year's dates? Great fun when the guru leaves and a true WTF.
None of which changes the fact that the business requirement (find people without a schedule) is perfectly reasonable and not a WTF.
If you can use subqueries then something like:
SELECT (fields)
FROM employees
WHERE emp_id NOT IN (SELECT emp_id FROM schedules WHERE (complex date-selection expression) )can work. But you can't always use this type of subquery, either due to the back-end database or the front-end reporting tool not supporting it.
Ok, So how do I code this one?
" will those of you who are playing in the
match this afternoon move your clothes down onto the lower peg immediately
after lunch, before you write your letter home, if you're not getting your
hair cut, unless you've got a younger brother who is going out this
weekend as the guest of another boy, in which case, collect his note
before lunch, put it in your letter after you've had your hair cut, and
make sure he moves your clothes down onto the lower peg for you."
-
@blakeyrat said:
Do you honestly think it's a bad idea for a database system to have a "timespan" data type, or are you just being a contrarian?
No, I think timespans are neat idea. It would save the hassle of manually creating some data type to fulfill that purpose then rely on custom-rolled functions to emulate that behaviour (I seem to remember doing that for a "duration" column years ago in Excel).
I was being sarcastic in the sense of "anything's possible if you want to arduously roll your own because the vendor hasn't thought this so commonplace as to have provided it natively".
@mightybaldking said:
Ok, So how do I code this one?
" will those of you who are playing in the match this afternoon move your clothes down onto the lower peg immediately after lunch, before you write your letter home, if you're not getting your hair cut, unless you've got a younger brother who is going out this weekend as the guest of another boy, in which case, collect his note before lunch, put it in your letter after you've had your hair cut, and make sure he moves your clothes down onto the lower peg for you."Simply hard-code it into "Jones? Stand over there, boy."
-
@Lorne Kates said:
WHERE
(e.start_dt between @start_dt AND @end_dt)
OR
(e.end_dt between @start_dt AND @end_dt)
OR
(@start_dt between e.start_dt AND e.end_dt)
OR
(@end_dt between e.start_dt AND e.end_dt)Redundant. Try...
WHERE
e.start_dt <= @end_dt
AND
e.end_dt >= @start_dtMuch simpler.
-
@mightybaldking said:
Ok, So how do I code this one?
" will those of you who are playing in the match this afternoon move your clothes down onto the lower peg immediately after lunch, before you write your letter home, if you're not getting your hair cut, unless you've got a younger brother who is going out this weekend as the guest of another boy, in which case, collect his note before lunch, put it in your letter after you've had your hair cut, and make sure he moves your clothes down onto the lower peg for you."You stop doing drugs.
