Page 1 of 1

Need suggestions for DB tables

Posted: Wed May 24, 2006 10:57 pm
by charp
I originally posted a question about this in the PHP Code forum, but later realized it belongs here. My apologies to the community.

So, here's the deal. I'm trying to gather some ideas about how best to build the following project for a high school web site:

Post the current day's bell schedule with the current period highlighted. The issues involved are 7 different bell schedules plus weekends and holidays with no bell schedules.

The specific dates for each bell schedule are known in advance, but how should I put everything in a database? One table for the various bell schedules and one for the dates on which each bell schedule is used? That part seems like the natural way to go. But the problem is how to I efficiently cross reference the two tables to determine which bell schedule to use?

Here's what I've formulated in my head so far for the table of dates -- Just two columns like this:

date | bell_schedule

The first query would select everything where the date column matches today's date. If there is a match, then a second query selects the specific bell schedule pulled from the first query. If there is no match, then it must be a non-school day and no bell schedule applies.

How does this sound for a start? Any ideas about how to make it more efficient than I've described?

Thanks in advance.

Posted: Thu May 25, 2006 7:20 am
by GM
Sounds about right to me, although I'm not sure what a bell schedule actually looks like in terms of the data it would contain... what is it? A list of times when the bell needs to be rung?

I'd have a table holding the id of the bell schedule, and the bell schedule itself:

id | bell_schedule


next, it's a question of how these bell schedules are distributed...

If there is a general pattern to the bell schedules (ie: Mondays, always has bell schedule 1, Tuesdays always has bell schedule 2 etc.) then it's probably better to hold a table of exceptions instead of all dates, check to see if today is an exception, and if not then use PHP to calculate what the bell schedule is according to it's normal rule. In this way, you don't have to maintain a table containing all the dates in the year, and you have only those dates on which the bell schedule doesn't follow the normal pattern (ie: school holidays, other "special" days).

Posted: Sat May 27, 2006 10:35 pm
by charp
GM wrote:Sounds about right to me, although I'm not sure what a bell schedule actually looks like in terms of the data it would contain... what is it? A list of times when the bell needs to be rung?
The bell schedules would indeed contain the times when the bell needs to be rung. Some schedules have as few as 6 ring times, others as many as 26 ring times. Each bell schedule is unique. In other words, no two bell schedules share anything in common with each other.
GM wrote:next, it's a question of how these bell schedules are distributed...
It's totally random here as well. Some schedules are on Friday's only, but there's no regularity to the Friday's on which they occur. It's that way through and through.

I did figure on filtering out weekend days and summer days before querying the database. The normal daily bell schedule is used about 85% of the time, so I'm board with your idea to include only the execption dates in the database.

So far, my thinking about how to set up these tables feels right. I do, however, appreciate all feedback and suggestions.