* 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
