Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Rob.

Asked: May 11, 2023 - 3:51 pm UTC

Last updated: May 22, 2023 - 5:02 pm UTC

Version: 23c

Viewed 10K+ times! This question is

You Asked

JSON Relational Duality views rely on a normalised, uniquely constrained schema to store the data in the 'current view' aka 'the last known state' aka 'snapshot'.

In my experience, the vast majority of schemas designed by developers do not conform to this pattern. For practical reasons (because the application needs more that just the last known state), schemas always include audit tables/history tables/timestamps/additional intersections between 1:m tables etc etc, all of which mean that unique keys (and therefore foreign keys) are NOT enforced in the database. So, how can JSON Relational duality views be of any practical use?

and Connor said...

In my experience, the vast majority of schemas designed by developers do not conform to this pattern


I'm probably going to disagree on this point to an extent. I agree that we often add a lot of "bloat" to our schemas - audit tables, history tables etc, my experience has been that these are in the main "additive" to the existing schema. Thus yes, an app might have EMP_HISTORY, DEPT_AUDIT tables etc, but they are still pinned around the 'core' EMP and DEPT tables, which will typically have the standard primary and foreign key norms that we expect.

A JSON duality view over EMP / DEPT gives

a) a JSON representation of these core apps tables, which is great for publishing data to REST services etc, and
b) lets Javascript devs push their representations of EMP ? DEPT into the database as JSON and then the triggers on those tables (which is invariable how audit tables etc are populated) will take care of the rest.

Don't get me wrong... like *all* database features, it can be mis-used or inappropriate in certain cases

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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.
Chris Saxon
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.


Chris Saxon
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?
Chris Saxon
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".

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library