[thelist] Storing Schedules in a database - Best Practices???

[Jay Turley]

Hi all-

I am working on a schema for a database which will hold work
schedules, i.e. dates and times that people will be working.

I'm not sure what the best way is to represent the scheduled shifts.

You could have a bit column for each shift, together with a date and a
foreign key for the worker.

You could have a varchar(X) field (where X is the number of shifts to
be managed) instead of the bit columns, or you could even have a INT
and use bitwise operations in the business logic.

Or you could have a shifts table and use it as foreign key into a
many-to-many table along with the foreign key from the worker table
and a date.

I'm curious if anyone out there has any experience with representing
this stuff and is willing to throw a few bones my way.

My biggest worry is getting the data for an entire month out in a
reasonable amount of time, given an organization with several hundred
workers.

Any help is greatly appreciated.

Thanks!

-Jay