JSON-Relational Duality Views
rob squire, May 17, 2023 - 8:30 am UTC
Thanks for your response Connor,
So from what you have said it seems that JSON-Relational Duality views are constrained to applications that work on 'Current View' data only.
Would you agree?
I say this because any unique keys on the 'additive' tables (history/audit) would need to include a date which then removes the ability to join them together in a meaningful way.
May 19, 2023 - 10:38 am UTC
I disagree - you can still join tables that have date columns in unique keys! I've done so many times myself.
Perhaps you could share an example which "removes the ability to join in a meaningful way"?
Joining TO history tables
Rob Squire, May 22, 2023 - 9:49 am UTC
Hi Connor,
Creating a Unique Key which includes a start date and an end date date is problematic because ....
the unique key does not guarantee that gaps and overlaps do not happen in your data.
joining TO such a table can only be on the actual date values stored in a row.
May 22, 2023 - 1:29 pm UTC
True, a unique key does not prevent gaps/overlaps. It is possible to make constraints to stop these though (as stated in my other reply).
joining TO such a table can only be on the actual date values stored in a row.
What exactly is the problem with this?
Constraints
rob squire, May 22, 2023 - 4:52 pm UTC
It is possible to make constraints to stop these though
Your blog is very nice, in it you show a technique for ensuring start/end dates are not overlapping across rows. However, you also mention several times that the technique is limited / doesnt work in practice
joining TO such a table can only be on the actual date values stored in a row.
What exactly is the problem with this?
Let's say you want to join FROM a row in an employee table TO a row in a departmant history table which has start date of say 01-JAN-2023 and end date of say 05-JAN-2023.
You can only INNER (equi-join) on those exact dates i.e. when the foreign key in the row in the employee table has those dates stored in it?
May 22, 2023 - 5:02 pm UTC
Please place all your comments on this topic in one thread - it makes it easier to follow. Ideally post a new question with a test case (create table + insert into) about making "meaningful joins".