How best to design a DB calendar

[ServerStorm]

Hi everyone,

I have been struggling with a calendar that I create from a MySQL DB. Currently I have the following mapping table:

CREATE TABLE `time2inventory2quantity` (
     `day_id` smallint(6) default NULL,
     `time_id` smallint(6) default NULL,
     `inventory_id` smallint(6) NOT NULL default '0',
     `quantity_id` smallint(6) NOT NULL default '0',
     `combine_inventory_id` smallint(6) default NULL,
     `club_id` tinyint(4) NOT NULL default '0',
     `season_id` tinyint(4) default NULL,
     `id` int(11) NOT NULL default '0',
     KEY `time_id` (`time_id`,`inventory_id`,`quantity_id`,`day_id`,`combine_inventory_id`,`club_id`)
   ) TYPE=MyISAM;

each entry in the time2inventory2quantity as t2i2q mapping table has relationships with the days_of_week, times, inventory, quantity, and season tables, each of which have ids that are mapped within the time2inventory2quantity table.

By entering the following

 INSERT INTO `time2inventory2quantiy` VALUES (2, 7, 19, 120, 0, 1, 4, 121);

in the t2i2q I can perform this join

SELECT i.product, i.description, i.price, q.quantity, t.time, dow.day_of_week, s.season
 				FROM time2inventory2quantiy AS t2i2q
   					INNER
 			 		 JOIN days_of_week AS dow ON t2i2q.day_id = dow.id
   					INNER
 			 		 JOIN times AS t ON t2i2q.time_id = t.id
   					INNER
 			 		 JOIN inventory AS i ON t2i2q.inventory_id = i.id
   					INNER
 			 		 JOIN quantity AS q ON t2i2q.quantity_id = q.id
   					INNER
 			 		 JOIN season AS s ON t2i2q.season_id = s.id
   			WHERE i.product = 'Bouncing Babies' AND s.id =4
   			OR i.product = 'Tumble Tots' AND s.id =4 
   			OR i.product = 'Busy Bees' AND s.id =4 
   			OR i.product = 'Grasshoppers' AND s.id =4 
   			OR i.product = 'Dragon Flies' AND s.id =4 
   			OR i.product = 'Gym School' AND s.id =4 
   			OR i.product = 'Parent Relief' AND s.id =4
   			ORDER BY q.id

and get the following result:

    Tumble Tots     Walking to 3 years     80.00     8     12:30 - 1:15     Tuesday     winter

I have used this to render a calendar from the returned dataset and formatting it in CSS and an html table.

The problem that I am facing is that for each season of the year there is a variable number of times on each given day of a week. I have run into a problem where I have had to update the Winter schedule two times and in each update I have had to remove or add times. When I add times I have to add it as the last t2i2q record and then I have to run multiple queries on the the calendar data to get the original times (lower record ids) and then the new time. This design means that everytime I need to add a new time I have to change the code on my web-site (adding the additional query). This obviously a poor design.


So to outline needs in a different way, I would like to be able to have the following:

• variable number of days in the week
• variable times for each individual day
• minimize the number of queries I need (to one query) but be able to add new time.

I know that it may be completely unresonable to reply to this but I was hoping that someone may have worked on a schedule like this and be able to share some of their insight in a more elegant way to design the tables and my queries.

Server Storm

[longneck]

i run in to this problem all of the time on sites that i run. after struggling with the exact same problems you're having, i setteld on this strategy:

i have two sets of tables to represent the scheduling. one table has the "rules" (meeting a is on every 3rd sunday at 6 pm, meeting b is every tuesday and thursday from 1 to 2:30 pm, etc.) and a second table for actual scheduled "events". the calendar always looks ahaed an arbitrary number of months, usually 6.

at the beginning of each month, the calendar administrator is responsible for logging in to the administration page and look at the calendar that the application proposes based on the rules. (this is handled in the back-end by using the rules table to actually insert the events in to the calendar table at the start of the review process, but setting the approved flag to 0.) the admin can then modify that month's calendar any way they want. when they have it the way they want, all of the items are marked approved.

one nice feature about this is that the admin can approve events that they know are ok to schedule, and not approve others. on a couple of sites, i aso have it set so that when an event or group of events is approved, it proposes an announcement to put on the home page, too.

in your case, i would set it up so that you have a repository of possible events (i will call them programs), and each one of those could have multiple rules. for example, your fitness class can have one entry in the programs table, and four rules: in the rules table every tue @ 2, every tue @ 4, every thur @ 2, every thur @ 4.

[ServerStorm]

Hi Longneck,

Thanks for the reply!

As per your last paragraph:

in your case, i would set it up so that you have a repository of possible events (i will call them programs), and each one of those could have multiple rules. for example, your fitness class can have one entry in the programs table, and four rules: in the rules table every tue @ 2, every tue @ 4, every thur @ 2, every thur @ 4.

Currently I have a table called Inventory. Inventory is anologus to the programs table that you describe. In inventory I have id, season_id (1-4 representing Spring, Summer, Fall, and Winter), Product (Name of Program), Description, and Price

Inventory (partial exerpt):
1 |1| Baby Gym |8 to 18 Months| 610.00
2 |1| Kindergym 1 |3 years old| 161.00
3 |1| Parent & Tot|18 months to 3 years| 161.00
4 |1| Kindergym 2 |3.5 to 4.5 years old| 161.00
5 |1| Kindergym 3 |4 to 5 years old| 161.00
6 |1| Kindergym 4 |Kindergym 3 graduates| 161.00
7 |1| Kindergym 5 |Kindergym 4 Graduates| 161.00
8 |1| Participation |Girls Grade 1 to 3| 161.00

I have a times table (partial exerpt)
1 9:00 - 9:45
2 9:00 - 10:00
3 9:15 - 10:00
4 9:15 - 10:15
5 9:30 - 10:00
6 10:00 - 11:00
7 10:15 - 11:00
8 10:15 - 11:05
9 10:15 - 11:15

I have a days_of_week table:
1 Monday
2 Tuesday
3 Wednesday
4 Thursday
5 Friday
6 Saturday
7 Sunday

This structure is somewhat similiar to what you describe in that I can in combination with my mapping table say find all inventory that exists at 10:15 - 11:00 on Sundays. However because these tables are joined in the mapping table, it a new program or time comes into play then I need to add it as the last mapping table id. When I do my query

SELECT i.product, i.description, i.price, q.quantity, t.time, dow.day_of_week, s.season
 				FROM time2inventory2quantiy AS t2i2q
    					INNER
 		 			JOIN days_of_week AS dow ON t2i2q.day_id = dow.id
    					INNER
 		 			JOIN times AS t ON t2i2q.time_id = t.id
    					INNER
 		 			JOIN inventory AS i ON t2i2q.inventory_id = i.id
    					INNER
 		 			JOIN quantity AS q ON t2i2q.quantity_id = q.id
    					INNER
 		 			JOIN season AS s ON t2i2q.season_id = s.id
    			WHERE i.product = 'Bouncing Babies' AND s.id =4
    			OR i.product = 'Tumble Tots' AND s.id =4 
    			OR i.product = 'Busy Bees' AND s.id =4 
    			OR i.product = 'Grasshoppers' AND s.id =4 
    			OR i.product = 'Dragon Flies' AND s.id =4 
    			OR i.product = 'Gym School' AND s.id =4 
    			OR i.product = 'Parent Relief' AND s.id =4
    			ORDER BY q.id

This creates the result set that is then looped through to create this pre-school gymnastic calendar

Is this NOT doing the same thing as you describe in your rules table or am I missing the difference in your set-up?

When I perform the query above I access the query in the following manner:

while($row = $result->fetch()){
                   //Bouncing Babies
              if ($row['day_of_week'] == 'Saturday' and $row['product'] == 'Bouncing Babies' and paspos($row['time'],'10') == 0){
                  $content .= isProgramFull($row['time'],$row['quantity']); 
                  }
  
    function paspos($base,$findme)
       {
           $result=strpos($base,$findme);
           if ($result===false) $result=-1;
           return $result;
   }
   
   function isProgramFull($passed_HTML,$quantity){
       
       if ($quantity > 0 and $quantity <= 3) { 
           return "<td class='ctf'>" . $passed_HTML . '</td>';
       }Else if ($quantity == 0) {
           return "<td class='status'>" . $passed_HTML . '</td>';
       }Else if ($quantity != 0) {
           return '<td>' . $passed_HTML . '</td>';      
       }
   } 

This is why when I add a new mapped line I have to perform the query more than once. It becomes innefficient. can you see more in the structure of my database or logic that needs to be changed to more closely match your suggestion?

Your ideas are appreciated.

truly,
ServerStorm