RE: calender table - time column?
guess this was too much information for you all! Or too complicated :) Well
this is what we came up with, I would like to know your opinions if you have
any? 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;
#
#
# 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;
#
#
# 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;
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';
$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++){
$query2 = select places from booking where id = '$i';
$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);
header(Location:
continue.php?day=$dbdayid=$dbidplaces=$place);
}
}else{
header(Location: booking.php?advice=sorry);
break;
}
}
}
Thanks
Andrew
-Original Message-
From: Andrew [mailto:[EMAIL PROTECTED]
Sent: 01 August 2003 17:26
To: Keith C. Ivey; [EMAIL PROTECTED]
Subject: RE: calender table - time column?
Thanks Keith here it is :)
The booking system will take the format of a form. Customer or travel agent can
select a date of travel. They will also input how many places are needed.
The system will then check that the selected dates are available. At this point
there are two possible responses.
1)
Places are not available: System will bring user back to 1st page and ask them
to choose another date.
2)
Places are available.
System will look at the date of the holiday and check to see if that day is a
Mon, Tue, Wed, etc. Depending on the result a list of possible flights
and costs
will be displayed (pulled from the data base)
User will choose their flight.
At this point the system will move the user to a payment area. Where
all details
are filled out and will be sent for payment and also e-mail sent to Admin with
booking details. The booking system will also now be updated so that
no one else
can book those dates (up to 24 persons rotating).
To deal with the travel agent commission a reference input field for the travel
agent to fill in on the e-mail/payment form. With a description similar to;
If you have a reference code
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,
RE: calender table - time column?
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
RE: calender table - time column?
Hi did anyone reply to this? Andrew I am currently in the process of building a calender/date MySQL table that will be searched for available dates for holiday tours? I want to use a time column as there will be a restriction on places for up to 3 days so this will have to be taken into account. Has anyone done something like this? Cheers Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.504 / Virus Database: 302 - Release Date: 24/07/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: calender table - time column?
On 1 Aug 2003 at 17:08, Andrew wrote: Hi did anyone reply to this? It doesn't look like it. Pretend you don't know anything at all about your project and try reading your message. Would you have any idea what sort of answer was wanted? If you want help, you have to explain exactly what's needed and provide details. Otherwise your message is likely to be ignored. I am currently in the process of building a calender/date MySQL table that will be searched for available dates for holiday tours? I want to use a time column as there will be a restriction on places for up to 3 days so this will have to be taken into account. Has anyone done something like this? Cheers Andrew -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: calender table - time column?
Thanks Keith here it is :) The booking system will take the format of a form. Customer or travel agent can select a date of travel. They will also input how many places are needed. The system will then check that the selected dates are available. At this point there are two possible responses. 1) Places are not available: System will bring user back to 1st page and ask them to choose another date. 2) Places are available. System will look at the date of the holiday and check to see if that day is a Mon, Tue, Wed, etc. Depending on the result a list of possible flights and costs will be displayed (pulled from the data base) User will choose their flight. At this point the system will move the user to a payment area. Where all details are filled out and will be sent for payment and also e-mail sent to Admin with booking details. The booking system will also now be updated so that no one else can book those dates (up to 24 persons rotating). To deal with the travel agent commission a reference input field for the travel agent to fill in on the e-mail/payment form. With a description similar to; If you have a reference code for this holiday, please type it here. (Help) The help will be linked to a pop up with more advice ect. The system will take the given date and check the number of places is available and if so will move to the next day and do it all again. When bookings are written to the table there will be 2 entries for each person each day. Entry one will be a reference number into one of the spare places to identify the user who is in that slot. The second entry will be amendment to the number of places available. This will be based on the itinerary and keep up to date available places and avoid booking where no places are left. Checks will be in place for bogus bookings (payment). This is based on the present itinerary and tour booking operation and may need further amendments to fit in with the live working model. - Ideally what I would like is a DB structure that would work or an idea of the table holding the Time/Date Thank you Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
