Airline Pilot Central Forums - Digital Logbooks

Quote:

Originally Posted by Blackwing (Post 3368597)

Can I see your schema?

You don't want to do what I did. Trust me. I'm afraid pg_dump -s contains doxing info. It's also 2441 lines since I used to import schedules, actual times, even US registered aircraft. PSA: Don't be a messy data hog. It's sufficiently normalized IMHO, with a role table for PIC/SIC/instructor/etc, an aircraft table for N12345/etc, an aircraft type table for C-152/etc. The general idea was to avoid logging multi-engine time and instead let that be a property of a type of an aircraft of a flight having a time. I also had a table for approaches, glides in a glider, and I took the FAA's requirement to log holding quite literally. I'll share some tables leaving out indices for fkeys and uniques.

Code:

CREATE TABLE public.flights (

   id integer NOT NULL,

   user_id integer NOT NULL,

   origin_airfield_id integer NOT NULL,

   destination_airfield_id integer NOT NULL,

   date date NOT NULL,

   aircraft_id integer,

   role_id integer,

   flight_number character varying(255) NOT NULL,

   actual_out timestamp without time zone,

   actual_in timestamp without time zone,

   actual_block integer,

   night integer,

   instrument integer,

   hood integer,

   cross_country boolean,

   remarks text,

   scheduled_out timestamp without time zone,

   scheduled_in timestamp without time zone,

   scheduled_block integer,

   created_at timestamp without time zone,

   updated_at timestamp without time zone,

   number_of_passengers integer,

   day_landings integer,

   night_landings integer,

   day_takeoffs integer,

   night_takeoffs integer,

   CONSTRAINT flights_actual_block_matches_in_out CHECK (((actual_block)::double precision = date_part('epoch'::text, (actual_in - actual_out)))),

   CONSTRAINT flights_actual_out_before_in CHECK ((actual_out <= actual_in)),

   CONSTRAINT flights_positive_values CHECK (((scheduled_block >= 0) AND (actual_block >= 0) AND (night >= 0) AND (instrument >= 0) AND (hood >= 0) AND (day_landings >= 0) A

ND (night_landings >= 0) AND (day_takeoffs >= 0) AND (night_takeoffs >= 0) AND (number_of_passengers >= 0))),

   CONSTRAINT flights_scheduled_block_matches_in_out CHECK (((scheduled_block)::double precision = date_part('epoch'::text, (scheduled_in - scheduled_out)))),

   CONSTRAINT flights_scheduled_out_before_in CHECK ((scheduled_out <= scheduled_in)),

   CONSTRAINT flights_times_within_actual_block CHECK (((night <= actual_block) AND (instrument <= actual_block) AND (hood <= actual_block)))

);



CREATE TABLE public.enroute_stops ( 

   id integer NOT NULL,

   flight_id integer NOT NULL,

   airfield_id integer NOT NULL,

   sequence integer NOT NULL,

   created_at timestamp without time zone,

   updated_at timestamp without time zone,

   user_id integer NOT NULL

);



CREATE TABLE public.airfields ( 

   id integer NOT NULL,

   user_id integer NOT NULL,

   name character varying(255),

   city character varying(255),

   state character varying(255),

   country character varying(255),

   latitude double precision,

   longitude double precision,

   elevation integer,

   timezone character varying(255),

   remarks text,

   created_at timestamp without time zone,

   updated_at timestamp without time zone,

   icao_code character varying(255),

   iata_code character varying(255),

   identifier character varying(255) NOT NULL,

   seconds_std_offset integer,

   seconds_dst_offset integer

);





CREATE TABLE public.approaches (

   id integer NOT NULL,

   flight_id integer NOT NULL,

   airfield_id integer NOT NULL,

   approach_type_id integer NOT NULL,

   runway character varying(255),

   ceiling character varying(255),

   visibility character varying(255),

   coupled boolean,

   missed boolean,

   created_at timestamp without time zone,

   updated_at timestamp without time zone,

   user_id integer NOT NULL

);



CREATE TABLE public.roles ( 

   id integer NOT NULL,

   user_id integer NOT NULL,

   identifier character varying(255) NOT NULL,

   created_at timestamp without time zone,

   updated_at timestamp without time zone,

   description character varying(255)

);



CREATE TABLE public.aircraft ( 

   id integer NOT NULL,

   user_id integer NOT NULL,

   remarks text,

   created_at timestamp without time zone,

   updated_at timestamp without time zone,

   aircraft_type_id integer,

   serial_number character varying(255),

   registration_number character varying(255),

   fleet_number character varying(255),

   identifier character varying(255) NOT NULL

); 



CREATE TABLE public.aircraft_types ( 

   id integer NOT NULL,

   user_id integer NOT NULL,

   identifier character varying(255) NOT NULL,

   description character varying(255),

   created_at timestamp without time zone,

   updated_at timestamp without time zone

); 



CREATE TABLE public.aircraft_properties ( 

   id integer NOT NULL,

   identifier character varying(255),

   description character varying(255)

); 





CREATE TABLE public.glides ( 

   id integer NOT NULL,

   flight_id integer NOT NULL,

   launch_type_id integer NOT NULL,

   duration integer,

   distance double precision,

   release_altitude double precision,

   max_altitude double precision,

   engine_start_altitude double precision,

   created_at timestamp without time zone,

   updated_at timestamp without time zone

);



CREATE TABLE public.holds ( 

   id integer NOT NULL,

   flight_id integer NOT NULL,

   location character varying(255),

   turns integer,

   duration integer,

   created_at timestamp without time zone,

   updated_at timestamp without time zone

);

Since writing this I've moved to a more CREATE ONLY mentality, where UPDATE is reserved for entry errors. Since dabbling in accounting I've come to see, for the better, table rows as journal entries first rather than objects. They are primarily verbs or events, not nouns or things. If a noun is to be represented then the facts must be true in all future cases. Logic should be (borrowing from prolog lingo) monotonic. Defeasible reasoning should be avoided. If I restrict my reasoning to CREATE while avoiding UPDATE/DELETE then I get reasonable normalization, almost no NULLs, and monotonicity. This is pretty straight forward in a logbook since it is essentially a journal. For sure I'd rip out that scheduled_ time crap. I'd also remove the multiuser stuff, which caused me to run mulit-column fkeys to ensure integrity. This is just offensive when using surrogate keys. On that, I might also move to natural keys. One downside of natural keys in web-apps (I use a web front end) is that there is no atomic update+move in REST, which is actually what you need when changing a primary key. In practice you can use a choice of POST/PUT/PATCH and return a redirect but it's not technically restful. CREATE ONLY logic defeats this conceptual problem too, by avoiding UPDATE.

Minimum 4 tables I'd recommend after exiting this rabbit hole: flights, aircraft, aircraft_types, roles. You can do the rest in a remarks field without undue ceremony or harm. Flights logs times that aren't a property of an aircraft or a seat/job and landings. Aircraft types would contain all the booleans you need such as multi-engine, tailwheel. PIC/SIC/instructor would be roles. Do these 4 tables and KISS.