CODEWITHSUNDEEP
sql-servervb.netsql-server-2005
Morglor
Morglor

Reputation: 347

How to store availability of practitioner (day of week, and time of day) in SQL Server

I am developing an application which will keep track of various information about medical practitioners that are affiliated with my place of work. One thing that we are tracking, is their availability by time of day (morning, afternoon, and evening) for each day of the week (monday - sunday). Additionally we will store an optional comment about the practitioners availability, something like "Does not work during full moon."

The input/display form for this information will be table with days of the week as column headers, and times of the day as row headers. Check boxes will indicate whether the practitioner is available during the specified time.

I am having a hard time figuring out how to store this information in the database. The most obvious solution to create a table with 24 columns (21 for 7 days * 3 times, 1 as the primary key, and 1 for the foreign key referring to the practitioner, and 1 for the comment). Which is a crappy table, but at least it's easy to select and update.

Another solution is to create a table with attributes:

That's a less ugly table, but it will be a pain to update, since I would have to have a separate update statement for each day of the week. The comment would also have to be stored outside of this table.

Yet another solution may be to have one column for each day of the week, with the datatype being BLOB (or whatever SQL Server's version of that is). And then store the time information as an array of size three. But I'm sure this breaks every rule of good database design. This would also limit the effectiveness of select queries.

Any ideas as to how to store this data?

EDIT:

To clarify, we are only interested in what their average week schedule is. Things like holidays and vacations can be ignored.

On the front end, the information will be displayed as a grid, with columns representing days of the week, and rows representing times of the day.

This information may need to be searchable. For example, we may want to pull up a report of all practitioners who work on Monday evenings.

Upvotes: 1

Views: 971

Answers (2)

Darryl Peterson
Darryl Peterson

Reputation: 2270

I would suggest the following design, a many-to-many relationship.

table: practitioners
- practitioner_id

table: schedule_blocks
- schedule_block_id

table: practitioners_to_schedules
- practitioner_id
- schedule_block_id

When I design, I'm using looking at the lifetime of the system and what are the probable changes to be added. This design requires a little more effort upfront, but saves effort over time.

A record is only written to the practitioners_to_schedules table when a practitioner is available. This could reduce the size of the table and can result a denser number of records per data/index page.

One advantage of the above design is that it does not hard-code the current schedule "chunking" into the schema. If in the future, the system were to break the "chunks" down into hours, the table schema would not change, just the contents of the tables.

Another advantage is this lends itself to the concept of effective dates. A physician is available every Monday morning, but is unavailable next week. Add effective_date and end_date to the relation table and the functionality is supported.

This lends itself to a relatively simple and indexing scheme. Two indexes on practitioners_to_schedules and you can report on most options efficiently. Depending on which reads are most common, you can decide which id to use for the clustered index.

Upvotes: 3

Jonathan M
Jonathan M

Reputation: 17451

Sounds like your second table design will meet your needs better:

practitioner_id :: int (foreign key and primary key)
day_of_week :: int (primary key)
morning :: bit
afternoon :: bit
evening :bit

Upvotes: 1

Related Questions