April 6, 2013 at 5:46 pm
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)
)
[/Code]
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!
April 11, 2013 at 12:40 am
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.
April 11, 2013 at 9:52 am
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
April 11, 2013 at 10:11 am
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply