I have found myselft several times in the need to create joints between tables. For instance, planning a trip with many cyclists in a relay, I have a skill table (speak publicly, bike nechanic, great cook…) and a period table (May, June…) and at any period, I want someone from the cyclist table with each skill. Other example: I have kids (table pupils) who pass several tests (table test) assessed according different skills (table skills). Third example: I have national sponsors that have local shops. Organising multiple events in different locations, I would like to check if my sponsors have nearby shops in each event location.
In the case of the tour planning, if I come to add a skill to the skill list, I would like all periods to automatically get a new (empty) row for me to know that I am missing a skill. Similarly, in the case of teaching, I would like a new test to be automatically exported into the “assessment” table. In the sponsor case, I would like a new empty sponsor line to be automatically added for each of the events in the “local sponsoring” table.
This could be achieved with a new type of table, “joints”, that are continuously produced as the crossing of two (or more) existing tables. If table A has n_A rows, and table B has n_B rows, the joint has n_A*n_B rows. If I add a row in the table A, I want n_B rows to be created in the rows, one for each row in B. If I remove a row in the table A, I want all the corresponding rows to be discarded.
Probably only the non-empty rows in a joint should be counted in the number-of-rows limit, because joints can become really big, really fast!
... View more