Question about Design-Concept

[Zvoni]

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?

[jmcilhinney]

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".

[Zvoni]

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!"

[techgnome]

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

[Zvoni]

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.

[GaryMazzone]

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

[Zvoni]

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!"

[GaryMazzone]

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