Database design help

[dawtashi8]

I am trying to develop a system to search the flight schedule:

 

Where the user will select the origin, destination, and date of departure/arrival (if it is round trip) then accordingly show the result.

 

As of now I have a single table to store the information of the flight. But there is some issues while searching. My table is as follows: I am using MySQL as my database.

 

Fields: id, origin, destination, transit (Y/N), flightNo, date, departure time, arrival time, airline.

 

The issue is if the flight is travelling to Bangkok from Paris via Amesterdam then the record will be :

 

1. Paris, Amesterdam, Y, 2014-01-08, Q123, 09:00, 12:00, Qartar

2. Amesterdam, Bangkok, N, 2014-01-08, Q123, 12:30, 15:30, Qarter

Similarly there is another record such as 

3. Paris, Bangkok, Y, 2014-01-08, Q123, 09:00, 12:00, Qartar

4. Bangkok, Singapore, N, 2014-01-08, Q123, 12:30, 15:30, Qarter

 

In such a case what should be the query, and would like to request if there is a need to improve my table design/how should I improve my table design so, that the result could be displayed without writing any complex queries. Please help.

 

Your help will be highly appreciated.

 

Regards,

 

Tashi

[Barand]

What is your current query - the one creating your issue?

 

Do you want to retrieve

 

• both the flight via Amsterdam and the direct one
• just the direct flight
• just the one via Amsterdam

It isn't clear what you want

Edited January 9, 2014 by Barand

[dawtashi8]

For instance, if a user is selecting the origin as Paris and the destination as Bangkok, then what the query should show is 

 

Paris -> Amsterdam -> Bangkok, since the flight is travelling to Bangkok via Amsterdam. The query should fetch all the details such as flight no and date (which will be same since it is the same flight travelling to Bangkok via Amsterdam), arrival date and Departure date at Amsterdam and Bangkok.

 

Regarding the query, I tried to write but did not get a logic to show the record as I wanted. So, I would like to request to kindly help me to get the solutions. 

 

My table schema:

 

-- Table structure for `tbl_flight_details`

-- ----------------------------

DROP TABLE IF EXISTS `tbl_flight_details`;

CREATE TABLE `tbl_flight_details` (

  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,

  `origin` varchar(100) NOT NULL,

  `destination` varchar(100) NOT NULL,

  `transit` enum('Y','N') DEFAULT 'N',

  `date` date NOT NULL,

  `flightNo` varchar(20) NOT NULL,

  `departureTime` time NOT NULL,

  `arrivalTime` time NOT NULL,

  `airlineId` int(2) NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=259 DEFAULT CHARSET=utf8;

[Psycho]

There is no easy solution here because the number of parameters is quite large. But, let's say you want to find a flight from one location to another. I think - at the very least - you need to determine the maximum amount of stops. For each potential stop you will want to create separate queries and then merge the results together using UNION. So, one query for a nonstop (if one exists). A second query for flights with one stop. A third for two stops, etc. Then for each query with one or more stops you would JOIN the table on itself using the destination of the first flight and the origination of the second flight PLUS, ensuring the flight end time of the first is before the start of the second flight.

 

EXAMPLE 1 (nonstop flights)

 

SELECT * FROM tbl_flight_details
WHERE origin = '$origin'
  AND destination = '$destination'

 

EXAMPLE 2 (one-stop flights)

 

SELECT *
FROM tbl_flight_details AS f1
JOIN tbl_flight_details AS f2
  ON f1.destination = f2.origin
  AND f1.date = f2.date
  AND f1.arrivalTime < f2.departureTime
WHERE f1.origin = '$origin'
  AND f2.destination = '$destination'

[dawtashi8]

Thank you very much. But still I am not getting the result as I wanted. Actually I am developing my application using CodeIngiter framework. Is there a required to make change in my current database table?  if so, what changes? From my side, I change 'transit' field to 'stop'. I wanted to display the record in two rows, for 1 stop: 

 

1. Paris, Amesterdam, 1, 2014-01-08, Q123, 09:00, 12:00, Qartar

2. Amesterdam, Bangkok, 0, 2014-01-08, Q123, 12:30, 15:30, Qarter

 

Moreover, I am still confused in using the Union between the two queries. Is it the union between the queries with nonstop flight and onestop flight? 

 

Your help will be highly appreciated.

 

Thanks

[Barand]

Something like this maybe

SELECT f1.origin
        , f1.destination
        , '' as via
        , f1.departure
        , f1.arrival
        , f1.flightNo
        , f1.airline
        FROM flight f1
        WHERE f1.origin='Paris'
            AND f1.destination = 'Bangkok'
            AND f1.date = '2014-01-08' 
        UNION
        SELECT f1.origin
        , f2.destination
        , f1.destination as via
        , f1.departure
        , f2.arrival
        , f1.flightNo
        , f1.airline
        FROM flight f1
            INNER JOIN flight f2 ON f1.destination=f2.origin AND f1.flightno = f2.flightno
        WHERE f1.origin='Paris'
            AND f2.destination = 'Bangkok'
            AND f1.date = '2014-01-08
+--------+-------------+-----------+-----------+----------+----------+---------+
| origin | destination | via       | departure | arrival  | flightNo | airline |
+--------+-------------+-----------+-----------+----------+----------+---------+
| Paris  | Bangkok     |           | 09:00:00  | 12:00:00 | Q123     | Qatar   |
| Paris  | Bangkok     | Amsterdam | 09:00:00  | 15:30:00 | Q123     | Qatar   |
+--------+-------------+-----------+-----------+----------+----------+---------+

Edited January 10, 2014 by Barand

[dawtashi8]

Thanks Barand, It worked