Hi Roger this was very useful indeed thank you. I would really like to know any other views on this design and implications for the future development of such a system in terms of flexibility
Andrew >-----Original Message----- >From: Roger Baklund [mailto:[EMAIL PROTECTED] >Sent: 02 August 2003 16:49 >To: [EMAIL PROTECTED] >Cc: [EMAIL PROTECTED] >Subject: Re: calender table - time column? > > >* Andrew >> guess this was too much information for you all! Or too >> complicated :) > >...or maybe too vague? ;) > >> Well this is what we came up with, I would like to know your >> opinions if you have any? > >ok, but be warned, I would have done this _totally_ different... :) > >> I can see two flaws in the design for the Time/Date so I >> really would be interested in any of your views :) >> >> CREATE TABLE booking ( >> id varchar(4) NOT NULL default '', >> month char(3) NOT NULL default '', >> date varchar(4) NOT NULL default '', >> day char(3) NOT NULL default '', >> year varchar(4) NOT NULL default '', >> one varchar(50) NOT NULL default '', >> two varchar(50) NOT NULL default '', >> three varchar(50) NOT NULL default '', >> four varchar(50) NOT NULL default '', >> five varchar(50) NOT NULL default '', >> six varchar(50) NOT NULL default '', >> seven varchar(50) NOT NULL default '', >> eight varchar(50) NOT NULL default '', >> nine varchar(50) NOT NULL default '', >> ten varchar(50) NOT NULL default '', >> eleven varchar(50) NOT NULL default '', >> twelve varchar(50) NOT NULL default '', >> thirteen varchar(50) NOT NULL default '', >> fourteen varchar(50) NOT NULL default '', >> fithteen varchar(50) NOT NULL default '', >> sixteen varchar(50) NOT NULL default '', >> seventeen varchar(50) NOT NULL default '', >> eighteen varchar(50) NOT NULL default '', >> nineteen varchar(50) NOT NULL default '', >> twenty varchar(50) NOT NULL default '', >> twentone varchar(50) NOT NULL default '', >> twentytwo varchar(50) NOT NULL default '', >> twentythree varchar(50) NOT NULL default '', >> twentyfour varchar(50) NOT NULL default '', >> places char(2) NOT NULL default '24' >> ) TYPE=MyISAM; >> # -------------------------------------------------------- > >You seem to use string column types for everything? You should use the >'correct' column types whenever possible, it will make the database faster >and lighter. > ><URL: http://www.mysql.com/doc/en/Column_types.html > > >Also, you should define indexes. In this early stage of your project all >needed index are not yet known, but you should at least define primary keys. > ><URL: http://www.mysql.com/doc/en/MySQL_indexes.html > ><URL: http://www.mysql.com/doc/en/CREATE_INDEX.html > > >month, date, day and year can/should be stored in a single column. > >The above table should imo have been split into three tables: > >CREATE TABLE booking ( > b_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, > booking DATETIME); > >CREATE TABLE person ( > p_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, > name VARCHAR(50)); > >This person table is probably the same thing as your 'guests' table below. >If so, add the needed columns from that table to the 'person' table, and >drop the 'guests' table (or the other way around, if you prefer). > >CREATE TABLE booking_person ( > b_id INT UNSIGNED NOT NULL, > p_id INT UNSIGNED NOT NULL, > booking_time TIMESTAMP, > PRIMARY KEY (b_id,p_id), > UNIQUE KEY (p_id,b_id)); > >The 'booking_time' column is just a suggestion, because it is maintained >'for free': the TIMESTAMP column type is 'magic' and is automatically set to >the current time when the record is created or changed. It could be usefull >to know _when_ a booking has been made, right? Read about the TIMESTAMP >datatype here: > ><URL: http://www.mysql.com/doc/en/DATETIME.html > > >To find how many persons are booked on a flight: > >SELECT COUNT(*) AS booking_count > FROM booking_person > NATURAL JOIN booking > WHERE booking.booking = '2003-08-10 15:30' > >NATURAL JOIN can be used in this example because there is only the 'b_id' >column name that is common between the two tables. Read about NATURAL JOIN >and other joins here: > ><URL: http://www.mysql.com/doc/en/JOIN.html > > >> # >> # Table structure for table `flights` >> # >> >> CREATE TABLE flights ( >> day char(3) NOT NULL default '', >> flightone varchar(5) NOT NULL default '', >> flighttwo varchar(5) NOT NULL default '', >> flightthree varchar(5) NOT NULL default '', >> flightfour varchar(5) NOT NULL default '' >> ) TYPE=MyISAM; >> # -------------------------------------------------------- > >This design will break when there are five or more flights any one day. >Also, there is no way to define exceptions: what if the next monday is a >national holliday, and some of the flights won't be flying? > >I guess your design reflects the normal situation: most mondays there are >four flights, at 06:00, 12:00, 15:30 and 18:00. You would not want to put a >record in the flights table for _every_ monday, but it would be nice to be >able to define exceptions, wouldn't it? Furthermore, weekdays seems to be a >relevant attribute in this system, usually it is only an output attribute, >created by the server during query execution, using >DATE_FORMAT(date_column,'%a') or similar. Read about DATE_FORMAT() and a >heap of other date & time related functions here: > ><URL: http://www.mysql.com/doc/en/Date_and_time_functions.html > > >Consider this design: > >CREATE TABLE flights ( > f_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, > flight_weekday CHAR(3) NOT NULL, > flight_time TIME NOT NULL, > UNIQUE (flight_weekday,flight_time)); > >I think you need the f_id column here, it should probably be inserted in the >'booking' table. Otherwise, the flight_weekday/flight_time combination >should be defined as the primary key. Note that this design does not put a >limit on the number of flights per day, there could be one every minute. > >Some records: > >INSERT INTO flights (flight_weekday,flight_time) VALUES > ('Mon','06:00'),('Mon','12:00'),('Mon','15:30'),('Mon','18:00'), > ('Tue','01:00'),('Tue','02:00'),('Tue','11:00'),('Tue','23:00'), > ('Wed','00:00'),('Wed','14:00'); > >...and so on (I'm lazy). > >The exceptions: > >CREATE TABLE flight_exceptions ( > e_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, > e_date DATE NOT NULL, > e_type ENUM('extra','cancelled') NOT NULL, > flight_time TIME NOT NULL, > reason VARCHAR(255) >); > >e_id because there should be a primary key. e_date is the exception date, >you could call it exception_date, if you don't mind the typing. You should >_not_ call it 'date', because that is a column type, and while it is not a >reserved word now, it may become so in the future. (You _could_ use it even >if it was reserved, using backticks, but it's awkward). > >e_date is the date of the exception. After the date has passed, delete the >old rows. This statement would keep exceptions for two weeks after they >occured, and delete anything older: > >DELETE FROM flight_exceptions WHERE e_date < NOW() - INTERVAL 14 DAY > >e_type is the exception type: it could be a 'cancelled' flight, or it could >be an 'extra' flight. If there are more types of exceptions, they could be >listed in the ENUM() definition. > >I don't know if 'reason' is relevant/known, but you get the idea, I hope. > >Some exception example rows: > >INSERT INTO flight_exceptions (e_date,e_type,flight_time,reason) VALUES > ('2003-08-11','cancelled','12:00','national holliday'), > ('2003-08-11','cancelled','15:30','national holliday'), > ('2003-08-11','cancelled','18:00','national holliday'), > ('2003-08-11','extra','07:00','national holliday'); > >Monday 11. is a national holliday (in this example), so every day-flight is >cancelled, but there is an extra morning-flight. > >When you query for the booking, and when you list the flights for any date, >you must also check the exception table. > >> # >> # Table structure for table `guests` >> # >> >> CREATE TABLE guests ( >> surname varchar(50) NOT NULL default '', >> firstname varchar(50) NOT NULL default '', >> email varchar(50) NOT NULL default '', >> phone varchar(50) NOT NULL default '', >> date varchar(15) NOT NULL default '', >> places char(2) NOT NULL default '', >> flight varchar(5) NOT NULL default '', >> ref varchar(50) NOT NULL default '' >> ) TYPE=MyISAM; > >This table have no obvious candidate for a primary key... what do you do if >you have two "Joe Smith" on the same flight? > >I suggest you move the surname, firstname, email, phone, places(?) and >ref(?) columns to the 'person' table decribed above. > >> INSERT INTO flights VALUES ('Mon', '06:00', '12:00', '15:30', '18:00'); >> INSERT INTO flights VALUES ('Tue', '01:00', '02:00', '11:00', '23:00'); >> INSERT INTO flights VALUES ('Wed', '00:00', '14:00', '', ''); >> INSERT INTO flights VALUES ('Thu', '08:23', '09:16', '17:21', '22:09'); >> INSERT INTO flights VALUES ('Fri', '03:55', '', '', ''); >> INSERT INTO flights VALUES ('Sat', '07:22', '13:45', '', ''); >> INSERT INTO flights VALUES ('Sun', '05:33', '09:40', '23:12', ''); >> >> $query = "select id,day from booking where month='$month' and >> date='$day' and >> year='$year'"; > >$query = "select b_id as id,DATE_FORMAT(booking.booking,'%a') as day > from booking where booking='$year-$month-$day'"; > >(It is not obvious from this code snippet why you need the 'day' column >here.) > >> $result = @mysql_db_query ($database, $query); >> if ($result){ >> $dbid = mysql_result ($result, 0, "id"); >> echo $dbid; >> echo '<br>'; >> for ($i = $dbid; $i < $dbid+5; $i++){ > >...hm... are you looping through this and the following few flights? > >> $query2 = "select places from booking where id = '$i'"; > >An assumption is made that the id numbers are in consecutive order, without >any missing id's? What if an operator makes a mistake, registers them in the >wrong order or similar? > >(See above on how to find the number of places, I guess you would need to >calculate 24 - booking_count) > >> $result2 = @mysql_db_query ($database, $query2); >> $dbplaces = mysql_result ($result2, 0, "places"); >> >> if ($place <= $dbplaces){ >> if ($i==$dbid+4){ >> $dbday = mysql_result ($result, 0, "day"); > >Ah, here comes the 'day'... :) > >> header("Location: >> continue.php?day=$dbday&id=$dbid&places=$place"); >> } >> }else{ >> header("Location: booking.php?advice=sorry"); >> break; > >I must admit I don't quite understand this PHP code, but this is a mysql >forum, so let's stick to the database issues, ok? > >I hope the above advices/links was usefull to you, > >-- >Roger > > >--- >Incoming mail is certified Virus Free. >Checked by AVG anti-virus system (http://www.grisoft.com). >Version: 6.0.505 / Virus Database: 302 - Release Date: 30/07/2003 > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
