SQL Novice Advice

Blue)(Fusion

Active Member
Mar 1, 2017
133
49
28
Chicago
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:
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;
And here's the two table structures:
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;
 

vl1969

Active Member
Feb 5, 2014
634
75
28
Without properly looking over this sql and knowing what sql engine used, I can not acert the desired result, but this sql looks scary.

Why not a simple join with group by?
 

Blue)(Fusion

Active Member
Mar 1, 2017
133
49
28
Chicago
Without properly looking over this sql and knowing what sql engine used, I can not acert the desired result, but this sql looks scary.

Why not a simple join with group by?
Good point. I am using MariaDB 10.6 with InnoDB. I also don't know how a join with group by would help (given I don't fully understand what I have even done yet).

The query is in a PHP page and the output is in an HTML table.

An example of the PHP output:
HTML:
    <table>
      <thead>
        <tr>
          <th><strong>Date</strong></th>
          <th><strong>Aircraft</strong></th>
          <th><strong>Type</strong></th>
          <th><strong>Origin</strong></th>
          <th><strong>Destination</strong></th>
          <th><strong>Flight Total</strong></th>
          <th><strong>Simulator Total</strong></th>
          <th><strong>PIC</strong></th>
          <th><strong>SIC</strong></th>
          <th><strong>ASEL</strong></th>
          <th><strong>AMEL</strong></th>
          <th><strong>Turbine</strong></th>
          <th><strong>Dual Received</strong></th>
          <th><strong>Dual Given</strong></th>
          <th><strong>Cross-Country</strong></th>
          <th><strong>Actual Inst.</strong></th>
          <th><strong>Sim. Inst.</strong></th>
          <th><strong>Night</strong></th>
          <th><strong>Landings Day</strong></th>
          <th><strong>Landings Night</strong></th>
          <th><strong>Approaches</strong></th>
          <th><strong>Person</strong></th>
          <th><strong>Remarks</strong></th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <td>2022-08-22</td>
          <td>N123AB</td>
          <td>B762</td>
          <td>KORD</td>
          <td>KSFO</td>
          <td>3:50</td>
          <td></td>
          <td></td>
          <td>3:50</td>
          <td></td>
          <td>3:50</td>
          <td>3:50</td>
          <td>3:50</td>
          <td></td>
          <td>3:50</td>
          <td></td>
          <td></td>
          <td>3:50</td>
          <td></td>
          <td>1</td>
          <td></td>
          <td>Jack Sparrow</td>
          <td>Flight644</td>
        </tr>
        <tr>
          <td>2022-08-22</td>
          <td>N123AB</td>
          <td>B762</td>
          <td>KSFO</td>
          <td>KLAX</td>
          <td>1:06</td>
          <td></td>
          <td></td>
          <td>1:06</td>
          <td></td>
          <td>1:06</td>
          <td>1:06</td>
          <td>1:06</td>
          <td></td>
          <td>1:06</td>
          <td></td>
          <td></td>
          <td>0:24</td>
          <td>1</td>
          <td></td>
          <td>1</td>
          <td>John Luc Picard</td>
          <td>Flight273, LOC 25L</td>
        </tr>
        <tr>
          <td>2022-08-23</td>
          <td>N354BM</td>
          <td>B763</td>
          <td>KLAX</td>
          <td>KDTW</td>
          <td>4:09</td>
          <td></td>
          <td></td>
          <td>4:09</td>
          <td></td>
          <td>4:09</td>
          <td>4:09</td>
          <td>4:09</td>
          <td></td>
          <td>4:09</td>
          <td></td>
          <td></td>
          <td></td>
          <td>1</td>
          <td></td>
          <td></td>
          <td>John Luc Picard</td>
          <td>Flight824</td>
        </tr>
          <tr>
          <td>TOTALS</td>
          <td></td>
          <td></td>
          <td></td>
          <td></td>
          <td>55:30</td>
          <td></td>
          <td></td>
          <td>55:30</td>
          <td></td>
          <td>55:30</td>
          <td>55:30</td>
          <td>21:58</td>
          <td></td>
          <td>55:30</td>
          <td>0:36</td>
          <td></td>
          <td>30:50</td>
          <td>9</td>
          <td>4</td>
          <td>2</td>
          <td></td>
          <td></td>
        </tr>
      </tbody>
    </table>
What I am doing is pulling some information from the current `flights` table and some from a separate `aircraft` table which are Boolean true'false. The IF statements in the above query uses the boolean (or enum) fields to then output NULL/0 or the total `flights.event_time` in that field.

I originally had some of the Boolean fields as normal SMALLINT fields which had the same numbers as the `flights.event_time` field but I also noticed some errors (of the human kind) that occured when inputting the date over the years into the spreadsheet this data was imported from. Perhaps I have made this a big mess and too complex - likely so. This is why I came here to see what a more seasoned developer may recommend. Go back to KISS INT data types and drop the IFs with sub queries?
 

amalurk

Active Member
Dec 16, 2016
276
97
28
101
It's been a long time since I did anything SQL for money but typically most applications will draw out the needed data with much simpler Select statements and then do more of the processing on the data in the programming language. So all those IF(Selects) and SUMs would usually be avoided and a lot more manipulating of the data would happen in PHP to arrive at what you need to show in the webpage.

The point of doing it the way I have suggested is you typically want to minimize the # of queries and the complexity of the SQL queries or the database server gets bogged down quickly as the application tries to scale. I suppose that doesn't really matter much for your personal application but if you then want to make interesting stuff you might put on web for others to try, you should probably start doing it the right way from the beginning.
 

Blue)(Fusion

Active Member
Mar 1, 2017
133
49
28
Chicago
It's been a long time since I did anything SQL for money but typically most applications will draw out the needed data with much simpler Select statements and then do more of the processing on the data in the programming language. So all those IF(Selects) and SUMs would usually be avoided and a lot more manipulating of the data would happen in PHP to arrive at what you need to show in the webpage.

The point of doing it the way I have suggested is you typically want to minimize the # of queries and the complexity of the SQL queries or the database server gets bogged down quickly as the application tries to scale. I suppose that doesn't really matter much for your personal application but if you then want to make interesting stuff you might put on web for others to try, you should probably start doing it the right way from the beginning.
Thank you for the feedback. This is what I was wondering...what is more common in the "professional" side: do the processing in SQL or in the program handling the response.
 

Dave Corder

Active Member
Dec 21, 2015
199
107
43
40
Thank you for the feedback. This is what I was wondering...what is more common in the "professional" side: do the processing in SQL or in the program handling the response.
I'm a DBA by trade these days, so I may be a bit biased here, but I'd argue the opposite - without getting into the weeds, RDBMS engines these days are extremely good at performing exactly this kind of processing (what with indexing, partitioning, parallelism, query optimizers, etc), especially when you start doing queries across multiple tables. I'd actually recommend doing as much of the calculations in SQL as possible and basically just have the PHP display the result set as returned from the database query. You may have to get a bit more complicated with your table designs, joins, use of aggregate functions, etc., but IMHO it'd be worth it.

Although to be fair, both approaches have their upsides and downsides. Putting all your logic in the application code does give you flexibility in choosing your database engine (e.g., if you wrote a lot of stored procedures in T-SQL or used a number of Oracle-specific functions or syntax in your code (I'm looking at you, "(+)" join notation...), then that does tie you to a particular engine...but if you're going to, say, shell out the cash for an Oracle RDBMS license or pay for AWS RDS instance, why not take full advantage of what it can give you instead of just using it as a dumb datastore?)

Going back a bit to your original problem, there are two parts to it - there's the design of the database schema itself to model the information you want to store, and then writing the queries to fetch the information you need from the database. They're distinct things yet intertwined and I'd suggest you spend some more time on the design (one thing I noticed right off the bat, for example, is there is a relationship between manufacturer and type but that's not reflected in your schema design - there's nothing in the database that would prevent you from entering a "Cessna B738" as a plane). My 2 cents.

Taking a different approach, since this seems like it's all fairly new to you, I'm going to plug something that I discovered a couple weeks ago... AirTable. It's kind of somewhere between a spreadsheet and a full on database and you get a lot of features from both. I learned about it when I started helping my wife with keeping track of fundraising activities and goals for our kids' co-op preschool - she needed something more advanced than the Google Sheet she was using, but I didn't want to build a full database and create a front-end for it. I stumbled upon AirTable while looking for other options and so far it's been pretty neat (even though in a sense it's a step backwards from the quote-unquote "real" RDBMS stuff I do in my day job). It, or one of the other cloud services like it, may allow you to play around and try things like how to collect and organize your data data and summarize it in a bit more user-friendly fashion than raw SQL + PHP. Then once you get the hang of table relationships and aggregate functions and such, you could then apply that knowledge to SQL. Just a thought.
 
  • Like
Reactions: vl1969