SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Question Regarding Implementing a Work Schedule Table


Question Regarding Implementing a Work Schedule Table

Author
Message
NuNn DaddY
NuNn DaddY
Old Hand
Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)

Group: General Forum Members
Points: 395 Visits: 490
Hello,

I have a question pertaining to the best way to design/add an addition to a database. The specs that I'm working with have a need for a work schedule table to indicate what days an employee works throughout the week. Currently, the assumption is that this is going to be the same for each and every week and the default should be Monday-Friday. Initially I had thought of building a separate table named "WorkSchedule" that would be something similar to the following:


create table WorkSchedule
(
EmployeeID int not null,
WeekDayName varchar(9) not null -- Using the names of the days as our clients aren't guaranteed to all provide the same integer representation,
constraint pk_WorkSchedule primary key (EmployeeID, WeekDayName),
constraint ck_WorkSchedule_WeekDayName check (WeekDayName in ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'))
)



I could then set the defaults via a trigger populating this table trigger or we could assume in the front-end that having zero records in the WorkSchedule table means to default to Monday-Friday. Being that the current specs only allow for a one week schedule, would it make more sense to add individual weekday columns to the Employee table so it would be something like the following?


create table Employee
(
EmployeeID int not null,
-- Other Columns For Employee Information (e.g. FirstName, LastName, etc..)
ScheduledMonday bit not null,
ScheduledTuesady bit not null,
-- All the way to friday
ScheduledFriday bit not null,
constraint pk_Employee primary key (EmployeeID)
)



Although this doesn't appear to be normalized anymore, it would allow me to easily default an employees work schedule using default constraints rather than triggers. However, this structure would limit the ability for expansion should the specs change (e.g. add the hours an employee works for each day). I'm thinking that having a separate table would allow for more flexibility in the likely event that the specifications will change, but I wanted to get some other thoughts on this. Thank you very much!
autoexcrement
autoexcrement
SSC-Addicted
SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)

Group: General Forum Members
Points: 400 Visits: 777
I'm just some guy, but it seems that the answer depends on the specifics of your situation.

A week will always have the same 7 days in it. That's not going to change. So I wouldn't particularly have a problem with creating a row column for each of those days as in your second example.

But if you think your situation will change from needing a simple yes/no per employee per day, then you would know that better than I would, and should design with that in mind.

I think your second example has the capability to handle that type of situation, however. Say you changed each of the "day" columns from a BIT to a TINYINT with an allowed value of 1-24, defaulting perhaps to 8. Then you could record how many hours each employee works each day.

If you need to allow for multiple "shifts" per employee per day, then you need to separate it out into its own table. But otherwise, I'd say separating it out--and using triggers and having to make inferences based on the absence of data--would be a mess.


"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura
Tom Thomson
Tom Thomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14636 Visits: 12238
autoexcrement (4/11/2013)
I'm just some guy, but it seems that the answer depends on the specifics of your situation.

Yes, that's certainly true. It almost always is.

A week will always have the same 7 days in it. That's not going to change. So I wouldn't particularly have a problem with creating a row for each of those days as in your second example.

I agree that there is no problem with the second example, but of course the OP is creating a column per day, not a row per day. NuNn DaddY's concern about it being denormalised is wrong though, it's not a repeating group in the sense banned in 1NF and it certainly doesn't violate the atomicity principle, and it appears to be fully normalised.
But if you think your situation will change from needing a simple yes/no per employee per day, then you would know that better than I would, and should design with that in mind.

I think your second example has the capability to handle that type of situation, however. Say you changed each of the "day" columns from a BIT to a TINYINT with an allowed value of 1-24, defaulting perhaps to 8. Then you could record how many hours each employee works each day.

restricting to 1-24 is silly for two reasons: first of all, at least some people may not work every day so you must allow 0. Second, it is common to work something other than whole hours - for example, some common working week lengths in UK are 40 hours, 37.5 hours, and 38.5 hours. 9 til 5:30 with a 45 minute lunch break (another common practise) is of course a 7.75 hour day. So restrict to 96 not 24, and measure in quarter hours instead of hours.
If you need to allow for multiple "shifts" per employee per day, then you need to separate it out into its own table. But otherwise, I'd say separating it out--and using triggers and having to make inferences based on the absence of data--would be a mess.

I tend to agree that separating it out should be avoided unless you want to record multiple amounts per day (because then you will almost certainly violate 1NF if you don't separate it out). But if it is separated out, there's no need for triggers provided things are properly encapsulated, so that the application sees an interface consisting of callable stored procedures and nothing else; there does need to be some discipline excercised by those who provide that interface and write those procedures, but triggers can be avoided. Of course if things aren't properly encapsulated triggers will be needed, as they will be even with good encapsulation if that encapsulation is done by means of non-updateable (in the MS sense of updateable - the things are of course updateable by using "instead of" triggers) views. Whether triggers are a bad thing and should be avoided is a question I refuse to take a view on (although personally I would prefer to avoid them, or at least to have only instead of triggers and those only on views, except when a trigger is used to capture audit information) but I do believe that application code should not be given sight of the schema, so strong encapsulation should be the objective. It's what used to be called "modular programming", which many database people now have forgotten about (or never heard of in the first place).

Tom

autoexcrement
autoexcrement
SSC-Addicted
SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)

Group: General Forum Members
Points: 400 Visits: 777
Sorry, that one issue was a typo on my part, meant to write "column" instead of "row" for days. I have edited that post.

The "1-24" was just an example of another way to handle it depending on his situation. Of course decimals would come in if he needs fractions of an hour. And 0 or NULL for any non-workdays.

Sorry for any confusion. Point was just that your design doesn't have to be limited to BIT.


"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search