I've been learning SQL in my free time and am trying to put it to practice by creating a flight logbook PHP page and Python for terminal entry.
I want to know if this structure and query setup makes sense to those with more practical knowledge. There's alot of IFs and not sure if it'd be wiser to just insert the numbers in their own column or what (like the written logbook) instead of using booleans and enums as much.
For the logbook PHP page which gets output in a table I have this query:
And here's the two table structures:
I want to know if this structure and query setup makes sense to those with more practical knowledge. There's alot of IFs and not sure if it'd be wiser to just insert the numbers in their own column or what (like the written logbook) instead of using booleans and enums as much.
For the logbook PHP page which gets output in a table I have this query:
SQL:
SELECT row_id,
flights.event_date,
flights.aircraft_id,
flights.origin,
flights.destination,
IF((SELECT aircraft_is_simulator FROM `aircraft` WHERE aircraft_id = flights.aircraft_id) IS FALSE, flights.event_time, NULL) as flt_time,
IF((SELECT aircraft_is_simulator FROM `aircraft` WHERE aircraft_id = flights.aircraft_id) IS TRUE, flights.event_time, NULL) as sim_time,
IF((SELECT aircraft_cat_class FROM `aircraft` WHERE aircraft_id = flights.aircraft_id AND aircraft_is_simulator IS FALSE) = 'ASEL', flights.event_time, NULL) as asel,
IF((SELECT aircraft_cat_class FROM `aircraft` WHERE aircraft_id = flights.aircraft_id AND aircraft_is_simulator IS FALSE) = 'AMEL', flights.event_time, NULL) as amel,
IF((SELECT aircraft_is_turbine FROM `aircraft` WHERE aircraft_id = flights.aircraft_id AND aircraft_is_simulator IS FALSE) IS TRUE, flights.event_time, NULL) as turbine,
IF(flights.position = 'pic', flights.event_time, NULL) as pic,
IF(flights.position = 'sic', flights.event_time, NULL) as sic,
flights.solo,
flights.dual_rcvd,
flights.dual_given,
IF(flights.is_xc IS TRUE, flights.event_time, NULL) AS xc,
flights.actual_inst,
flights.sim_inst,
flights.night,
flights.landings_day,
flights.landings_night,
flights.approaches,
flights.person,
flights.remarks,
aircraft.aircraft_type
FROM `flights`
INNER JOIN `aircraft` ON aircraft.aircraft_id = flights.aircraft_id
UNION ALL SELECT
NULL,
'TOTALS',
'---',
'---',
'---',
SUM(IF((SELECT aircraft_is_simulator FROM `aircraft` WHERE aircraft_id = flights.aircraft_id) IS FALSE, flights.event_time, 0)),
SUM(IF((SELECT aircraft_is_simulator FROM `aircraft` WHERE aircraft_id = flights.aircraft_id) IS TRUE, flights.event_time, 0)),
SUM(IF((SELECT aircraft_cat_class FROM `aircraft` WHERE aircraft_id = flights.aircraft_id AND aircraft_is_simulator IS FALSE) = 'ASEL', flights.event_time, 0)),
SUM(IF((SELECT aircraft_cat_class FROM `aircraft` WHERE aircraft_id = flights.aircraft_id AND aircraft_is_simulator IS FALSE) = 'AMEL', flights.event_time, 0)),
SUM(IF((SELECT aircraft_is_turbine FROM `aircraft` WHERE aircraft_id = flights.aircraft_id) IS TRUE, flights.event_time, 0)),
SUM(IF(flights.position = 'pic', flights.event_time, 0)),
SUM(IF(flights.position = 'sic', flights.event_time, 0)),
SUM(flights.solo),
SUM(flights.dual_rcvd),
SUM(flights.dual_given),
SUM(IF(flights.is_xc IS TRUE, flights.event_time, 0)),
SUM(flights.actual_inst),
SUM(flights.sim_inst),
SUM(flights.night),
SUM(flights.landings_day),
SUM(flights.landings_night),
SUM(flights.approaches),
'---',
'---',
'---'
FROM `flights` ORDER BY event_date, row_id ASC;
SQL:
CREATE TABLE `aircraft` (
`aircraft_id` char(8) NOT NULL,
`aircraft_type` enum('B762','B763','B738','B38M','CRJ2','CRJ7','CRJ9','C172','PA28','P28A') NOT NULL,
`aircraft_cat_class` enum('ASEL','AMEL') NOT NULL,
`aircraft_manufacturer` enum('Boeing','Bombardier','CAE','Cessna','Piper') NOT NULL,
`aircraft_model` char(16) NOT NULL,
`aircraft_year` year(4) DEFAULT NULL,
`aircraft_is_complex` tinyint(1) UNSIGNED NOT NULL DEFAULT 0,
`aircraft_is_turbine` tinyint(1) UNSIGNED NOT NULL DEFAULT 0,
`aircraft_is_simulator` tinyint(1) UNSIGNED NOT NULL DEFAULT 0,
`aircraft_is_121` tinyint(1) UNSIGNED NOT NULL DEFAULT 0,
`aircraft_operator` enum('place1','place2','place3','place4','place5') DEFAULT NULL,
`aircraft_ac_panel` enum('Classic','Flat','Flat/NPS','LDS') DEFAULT NULL,
`aircraft_ac_js` enum('3','4','super') DEFAULT NULL,
`aircraft_has_gps` tinyint(1) DEFAULT 0,
`aircraft_has_satcom` tinyint(1) DEFAULT 0,
`aircraft_has_hf` tinyint(1) DEFAULT 0,
`aircraft_has_acars` tinyint(1) DEFAULT 0,
`aircraft_has_cpdlc` tinyint(1) NOT NULL DEFAULT 0,
`aircraft_engine_make` enum('GE','P&W','Lycoming') DEFAULT NULL,
`aircraft_engine_model` char(18) DEFAULT NULL,
`aircraft_engine_submodel` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `flights` (
`row_id` smallint(5) UNSIGNED NOT NULL,
`event_date` date NOT NULL,
`aircraft_id` char(8) NOT NULL,
`origin` char(4) DEFAULT NULL,
`intermediate_1` char(4) DEFAULT NULL,
`destination` char(4) DEFAULT NULL,
`event_time` smallint(6) UNSIGNED DEFAULT NULL,
`position` enum('pic','sic') DEFAULT NULL,
`solo` smallint(6) UNSIGNED DEFAULT NULL,
`dual_rcvd` smallint(6) UNSIGNED DEFAULT NULL,
`dual_given` smallint(6) UNSIGNED DEFAULT NULL,
`is_xc` tinyint(1) UNSIGNED NOT NULL DEFAULT 0,
`actual_inst` smallint(6) UNSIGNED DEFAULT NULL,
`sim_inst` smallint(6) UNSIGNED DEFAULT NULL,
`night` smallint(6) UNSIGNED DEFAULT NULL,
`landings_night` tinyint(2) UNSIGNED DEFAULT NULL,
`landings_day` tinyint(2) UNSIGNED DEFAULT NULL,
`approaches` tinyint(1) UNSIGNED DEFAULT NULL,
`hold_proc` tinyint(1) UNSIGNED NOT NULL DEFAULT 0,
`person` varchar(255) DEFAULT NULL,
`remarks` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;