daily openings and closings

[jmr3460]

I am working on a site that will allow users to schedule events for one week at a time. Each day they can schedule an opening and closing time. My current table is structured like this:

  `event_id` int(3) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(3) NOT NULL,
  `date_start` varchar(10) NOT NULL,
  `day1_open` varchar(10) NOT NULL,
  `day1_close` varchar(10) NOT NULL,
  `day2_open` varchar(10) NOT NULL,
  `day2_close` varchar(10) NOT NULL,
  `day3_open` varchar(10) NOT NULL,
  `day3_close` varchar(10) NOT NULL,
  `day4_open` varchar(10) NOT NULL,
  `day4_close` varchar(10) NOT NULL,
  `day5_open` varchar(10) NOT NULL,
  `day_5close` varchar(10) NOT NULL,
  `day6_open` varchar(10) NOT NULL,
  `day6_close` varchar(10) NOT NULL,
  `day7_open` varchar(10) NOT NULL,
  `day7_close` varchar(10) NOT NULL,

After looking at scripting from this table it is looking a little difficult.

My question is, "Can anyone suggest a more simpler structure?"

Thanks for any ideas.

[Mchl]

Have one table for event data like:

 

eventID, name, dateStart, dateEnd  (BTW, use DATE type for storing dates)

 

and another to store information about opening and closing times like

 

eventID, day (DATE), openTime, closeTime (again, use TIME for storing times)

[jmr3460]

Thanks for the reply,

All of the events are the same, just different addresses. (I am getting the location from the users profile.) I created two new tables (event, open_close). This is the information for the tables I have now.

CREATE TABLE IF NOT EXISTS `event` (
  `event_id` int(3) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(3) NOT NULL,
  `date_start` date NOT NULL,
  `date_end` date NOT NULL,
  PRIMARY KEY (`event_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `open_close` (
  `open_id` int(3) unsigned NOT NULL AUTO_INCREMENT,
  `event_id` int(3) NOT NULL,
  `day` date NOT NULL,
  `open_time` time NOT NULL,
  `close_time` time NOT NULL,
  PRIMARY KEY (`open_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

I have in the past had a little trouble converting mysql time to php time but I will see what it takes and conform. I do want to do it the best way. I am going to do this in a two step process, but that is probably a php thing so I will bring that up their if I have any problems with that.

Thanks for any and all of your help.