dcsimg
Results 1 to 8 of 8

Thread: Question about Design-Concept

  1. #1

    Thread Starter
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,919

    Question about Design-Concept

    Hi Folks,
    i was asked by my skydiving club to design a database (incl. a Frontend written in Lazarus/Freepascal) to manage our students.
    Development is going to be probably on a SQLite-DB, Rollout later on probably MySQL.

    Now i'm struggling with a design-concept.
    Background:
    What people consider a "Parachute" actually consist of 4 components:
    1) AAD (Automatic Activation Device. "Why did you wake me up from my nap?")
    2) Harness (The thing you actually wear on your back)
    3) A reserve canopy ("Oh Crap! Have to reboot")
    4) Main Canopy ("Yeah. *celebrate*")

    So i was thinking a separate table for each component (linked through by foreign key to a (master)-manufacturer table), with columns needed per component (different columns for each component).
    Example:
    tbl_harness
    ID
    Manufacturer_ID
    Model
    SerialNo
    SomeMoreColumns

    the same for the other components

    Now, as above: a "parachute" consists of those 4 components assembled into a what we call a "Rigg"
    So i was thinking about a table for the Rigg
    tbl_rigg
    ID
    AAD_ID
    Harness_ID
    Reserve_ID
    Main_ID
    RiggNumber
    SomeMoreColumns

    Now, our students get assigned a Rigg prior to jumping ("Mike, you can use Rigg #5 for your next jump"), and they have to keep TWO Logbooks.
    Their personal Logbook, and the Main Logbook of the school (which is a big part of this database)
    In those Logbook(s) it's mandatory to enter, which Rigg you jumped

    So, i was thinking:
    tbl_logbook
    ID
    Student_ID
    Rigg_ID
    SomeMoreColumns

    And now i have the problem:
    For whatever reasons, a Rigg can get changed within time.
    Example:
    I did my last 7 Jumps with Rigg #5 in September2019, and it's entered that way in my tbl_Logbook.
    Now, last week, another Student jumped that Rigg #5 and had to cut away the Main Canopy.
    And the Main Canopy got destroyed, so we had to replace it. (THIS IS AN EXAMPLE. NOT EVERDAY-OCCURANCE! )

    Now, if i change the assembly of Rigg #5 from, say, Main_ID=5 to Main_ID=8 it would change the History of my Logook (and of any other Student who jumped that Rigg in the Past).

    So back to the drawing-board:
    In tbl_logbook, instead of a Rigg_ID FK, i thought to actually use all 4 Foreign Keys as in tbl_rigg, but in that case i would have to introduce a "deny delete" to the individual components-table, and probably introduce a column like "Retired" or similiar (probably a Date-Field).

    I was also thinking, that since a Logbook-Entry is fixed (no changing afterwards) concerning used Rigg, JumpDate, Location, Aircraft (and some other fields) i could simplify it by just grabbing the text from the components-tables and instead of 4 FK-columns to have one Text-column.

    So, any thoughts how to keep data fixed in one table, while other table(s) are dynamic but feed that data?
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,995

    Re: Question about Design-Concept

    One option would be to stick with your original idea but also keep a history of Rigg changes in another table. Another option would be to allow RiggNumber values to be duplicated and have date range to specify the validity of a record, so there would be two records with 5 as the RiggNumber with different, non-overlapping date ranges.

    By the way, where does the name "Rigg" come from? If I heard it spoken, which I probably have, I'd have assumed "rig".

  3. #3

    Thread Starter
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,919

    Re: Question about Design-Concept

    Quote Originally Posted by jmcilhinney View Post
    One option would be to stick with your original idea but also keep a history of Rigg changes in another table. Another option would be to allow RiggNumber values to be duplicated and have date range to specify the validity of a record, so there would be two records with 5 as the RiggNumber with different, non-overlapping date ranges.
    Mc, thx for your insight. It comes close to initial thoughts i had on that problem.
    What i wanted to avoid is that there are multiple entries for a RiggNumber, albeit i know that those entries would be in fact unique in its own if you consider the 4 Foreign Keys as a kindof combined PrimaryKey.
    Right now, my preferred option is the one with the 4 Foreign keys in the Logbook.
    After thinking about it (and the other solutions), i came to the conclusion, that in any case i would have to introduce a "deny delete"-mechanism to either the components-tables and/or the Rigg-table, if there is a single instance of that component/rigg being used by anyone at any time in the past. And something like a "Retired"-Date does look good for documentation-purposes

    EDIT: Just to make sure i understood you correctly:
    In your first approach (the one with the history-table), my Foreign Key tbl_logbook.Rigg_ID would actually point to the ID of that "historical" table?
    Kinda like the Rigg-table as a template, but by hitting the saveLogbook-Entry-Button it switches to the history-table?

    By the way, where does the name "Rigg" come from? If I heard it spoken, which I probably have, I'd have assumed "rig".
    Call it "germanised English!"
    Last edited by Zvoni; Oct 9th, 2019 at 06:52 AM.
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  4. #4
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,455

    Re: Question about Design-Concept

    What about denormalizing the data? When a rigg is assigned to the student, instead of simply assigning the id of the rigg in the record, copy the rigg components to the record.... then you would have a permanent record of the rigg, what components it comprised of, at that time, regardless o what happens to is afterwards. It essentially treats your logbook as a dataware house, a permanent record of what was what and when, which is, for all intents and purposes, what it should be. If you think about it, it's what a paperized version would be.


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5

    Thread Starter
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,919

    Re: Question about Design-Concept

    Quote Originally Posted by techgnome View Post
    What about denormalizing the data? When a rigg is assigned to the student, instead of simply assigning the id of the rigg in the record, copy the rigg components to the record.... then you would have a permanent record of the rigg, what components it comprised of, at that time, regardless o what happens to is afterwards. It essentially treats your logbook as a dataware house, a permanent record of what was what and when, which is, for all intents and purposes, what it should be. If you think about it, it's what a paperized version would be.


    -tg
    tg, that's basically my approach with the 4 Foreign Keys from the Rigg-table to copy them into the Logbook (replace (and expand) tbl_logbook.Rigg_ID with tbl_logbook.AAD_ID, tbl_logbook.Harness_ID etc.)
    And yes, the Logbook is a permanent record since the students still have to keep their personal logbook, and that one is "analogue" (a.k.a a Paper-Booklet). And both have to match each other!

    If you meant: Copy all data (columns/fields) of each component of the used Rigg to a logbook-entry (and produce redundancy in following entries -> e.g. 7 jumps in a row with the same rigg), i'm not really sure that's worth it, if i can solve it either with a "history"-table or with just the 4 foreign keys if i ensure a "deny delete" on each component if it was ever in use.
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  6. #6
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,349

    Re: Question about Design-Concept

    This sounds to me like a Type 3 data warehouse example... the rigg can be changed (different components) and be active for a specified date range. So I guess I would have a startDate and endDate on the table for a rigg makeup.. the start data can't be null and the enddate can be. When you change the makeup of a rigg you would mark the end date on the currently active one and create a new one.

    On the logbook you would have a date that the rigg was used and that can be used as it would fall between a start and end date on the rigg. So you would also know what a rigg was made up of on the day of use
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  7. #7

    Thread Starter
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,919

    Re: Question about Design-Concept

    Quote Originally Posted by GaryMazzone View Post
    This sounds to me like a Type 3 data warehouse example... the rigg can be changed (different components) and be active for a specified date range. So I guess I would have a startDate and endDate on the table for a rigg makeup.. the start data can't be null and the enddate can be. When you change the makeup of a rigg you would mark the end date on the currently active one and create a new one.

    On the logbook you would have a date that the rigg was used and that can be used as it would fall between a start and end date on the rigg. So you would also know what a rigg was made up of on the day of use
    GM, that matches the second approach of jmc's post.
    Hmm, so basically it would boild down to a SQL like ".... WHERE tbl_logbook.RiggNumber=tbl_Rigg.RiggNumber AND JumpDate Between tbl_Rigg.StartDate AND tbl_Rigg.EndDate"
    Off the bat: i would have to look up how BETWEEN works if EndDate IS NULL.

    So, right now, if i'm counting right, i'd say i have three options how to skin that cat.
    1) the history-table. the tbl_logbook.Rigg_ID points to the tbl_Rigg.ID of that History-table instead of the actual "regular" tbl_rigg. Which implies that once a tbl_rigg.ID is used for the first time it has to be copied to the history-table
    2) copy the 4 foreign keys directly 1:1 to the logbook.
    3) Allow multiple entries for a RiggNumber, but with Start- and EndDate, and thus the Logbook-Queries as above.

    In any case of those 3: once a component is used (in which Rigg whatsoever) it must have a "deny delete"-mechanism.

    EDIT: After thinking about it, the way with the history-table looks better and better, since i can implement the "deny delete" with a simple "Does this Component-ID (whichever of the 4) exists in the History-Table? If no, then it was never used. Delete allowed. If yes, then BEEEEPPPPP! Delete not allowed. Component was used in the past!"
    Last edited by Zvoni; Oct 9th, 2019 at 08:58 AM.
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  8. #8
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,349

    Re: Question about Design-Concept

    for end date of NULL I would use ISNULL(endDate,'12/31/9999')
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width