Fixed Timetable DB Design

views:

answers:

Fixed Timetable DB Design

I want to let my users specify which hours / days they want to be contacted, I though of creating a fixed timetable with the 7 days of the week and let the user specify which hours he has free.

I'm having a little trouble figuring out how I would store that info in the database, can anyone help me with a good table design for this situation?

+1 A:

I assume dates are not involved. If dates are involved, remove day and change the time type to date-time types.

So you need entries that look like this·

person_id - day(int 0-7) - time_low - time_high.

Thats all you should need to represent the data. Availability is represented by the ranges between time_low and time_high.

You will need application logic to merge overlaps and resolve overlaps. It should not be too hard.

Hassan Syed 2009-12-02 23:55:48

+1 A:

The Day table contains list of days (Sun, Mon, Tue..), you can also use enumerated field instead.
The Period table contains list of available (hourly) periods, or you could also use part of a day.

alt text

Damir Sudarevic 2009-12-03 13:35:08

redundant normalization....

Hassan Syed 2009-12-03 16:47:18

Thanks for the down-vote @Vainstah; where you see "redundant normalization", I see pre-loaded look-up tables. Where you need to sort-out "overlaps" and duplicate entries "with application logic" in your solution (see below), I see a problem which should not exists in the first place.

Damir Sudarevic 2009-12-03 17:44:52

Nothing personal, but both our models capture the same semantics. You've just created a over-the-top data model. In order for these tables to be useful I need to query Day - Contact and Period. That is an academic view of data-modelling not a practical view. Your sollution also requires -- the same -- logic to prevent overlap.

Hassan Syed 2009-12-08 12:45:48

ansaurus

tags:

views:

answers:

Fixed Timetable DB Design

related questions