How to ?

[jad]

<?php
guys I have the following DB structure
Now what is the best method/field whatever to assign crew to a flight?
I'm asking about the logic of assigning the crew to the flights list.

CREATE TABLE `crew` (
  `id` int(3) NOT NULL auto_increment,
  `title` varchar(50) NOT NULL default '',
  `name` varchar(250) NOT NULL default '',
  `mobile` varchar(250) NOT NULL default '',
  `email` varchar(250) NOT NULL default '',
  `idno` varchar(50) NOT NULL default '',
  `simu` date NOT NULL default '0000-00-00',
  `prof` date NOT NULL default '0000-00-00',
  `route` date NOT NULL default '0000-00-00',
  `medi` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=26 ;


CREATE TABLE `flights` (
  `id` tinyint(10) NOT NULL auto_increment,
  `date` date NOT NULL default '0000-00-00',
  `flight_no` varchar(50) NOT NULL default '',
  `std_a` varchar(50) NOT NULL default '',
  `std_a_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `sta_a` varchar(50) NOT NULL default '',
  `sta_a_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `std_b` varchar(50) NOT NULL default '',
  `std_b_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `sta_b` varchar(50) NOT NULL default '',
  `sta_b_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `break` varchar(250) NOT NULL default '',
  `pu_time` datetime NOT NULL default '0000-00-00 00:00:00',
  `qaia_to` datetime NOT NULL default '0000-00-00 00:00:00',
  `qaia_from` datetime NOT NULL default '0000-00-00 00:00:00',
  `remarks` text NOT NULL,
  `pending` char(2) NOT NULL default '0',
  `actypereg` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=8 ;

[devinemke]

what is the relationship between flights and crews? one-to-one? one-to-many? many-to-many? in other words, is there only one crew per flight or can a single flight have multiple crews? can one crew do mulitiple flights?

this is all the basics of data relationships.

[jad]

Hi
ah well
As you see in flights table
each flight has date and date/time for STD/STA (Station of departure and station of arrival)
Now
Many Crew should be added to each flight
but not same time.. anyway timing is php coding thingy not DB layer thingy

so in other words
a single flight can have multiple crew
and
single crew can have multiple flights
but not in the same time.

[devinemke]

Originally posted by jad
...a single flight can have multiple crew and single crew can have multiple flights but not in the same time.

a standard many-to-many relationship. you need to add a 3rd table that looks like this:

CREATE TABLE `crew_flight_link` (
`link_id` int(3) NOT NULL auto_increment,
`crew_id` int(3) NOT NULL default '',
`flight_id` int(3) NOT NULL default '',
PRIMARY KEY  (`link_id`)
) TYPE=MyISAM;

this table stores every relationship between every crew and every flight. you can then do table JOINS to see which crews are on which flights. as far as the timing, you need to add application layer logic to how records are inserted to prevent the same crew from being assigned to 2 different flights at the same time.

[jad]

hmm nice

CREATE TABLE `crew_flight_link` (
`link_id` int(3) NOT NULL auto_increment,
`crew_id` int(3) NOT NULL default '',
`flight_id` int(3) NOT NULL default '',
PRIMARY KEY  (`link_id`)
) TYPE=MyISAM;

lets say we have
flight 3308
and the crew list is
John
Jad
Nancy

I'll have to insert 3 records to the crew_flight_link ?
and if I have more crew on that flight I will have to insert more records?

hmm isnt it complex?

especially when I want to calculate the duty time for every single crew?

[devinemke]

Originally posted by jad
lets say we have
flight 3308
and the crew list is
John
Jad
Nancy

I'll have to insert 3 records to the crew_flight_link?

yes

Originally posted by jad
and if I have more crew on that flight I will have to insert more records?

yes

Originally posted by jad
hmm isnt it complex??
especially when I want to calculate the duty time for every single crew?

not really. again this is pretty standard way of doing many-to-many data relationships. be sure to properly index your crew_flight_link table to speed up your queries.

[jad]

hmm
so if I want to check to avoid having same crew on a flight in the same time I'll have to loop in a certain date
then get the flight(s) id's for that date
select Departure date/time and arrival date/time and then
get the crew list of that flight and compare it with other flights in same departure/arrival date/time
?

[devinemke]

let's say you have a new flight and need to book a crew. first you create then new flight record with all of it's date info. then query the link and flight tables to get all crews not on that date. those then are the available crews for that new flight.

[jad]

hmm
still cant get it, anyway, I will try it
I'm writing the app layer now to check doublicated crew entry for same time

Thank you mate.

[nemonoman]

Jad -- what airline are you writing this for?

[jad]

nemonoman ? does it really matter ?

I would like to keep their name private, till I finish the project.

[nemonoman]

No problem, Jad. Based on the state of this code so far, I thought I'd try to avoid the airline until your project is complete.

[jad]

Hi
heh
well its up to you
but based on your thoughs, you are not supposed to use Microsoft windows since you dont know how its coded and you dont even have the right to look inside.

thats the power of open source.. you can see everything.

[nemonoman]

On the other hand, when my PC crashes, I walk away and have a beer.