Concurrent-temporal modeling and the "mother of all time traveling functions"

cccv

Please see this concurrent-temporal example, especially the function named "pvpvrFI_FinancialInstrument".

Since this function's SELECT statement uses "LEFT JOIN", the returned table rows may have NULLs, correct?

Suppose we added Posit and Annex tables for a new "Name" attribute for the financial instrument entity/anchor, and the assembled view of the attribute is named "FI_NAM_FinancialInstrument_Name". How would we modify pvpvrFI_FinancialInstrument to incorporate information about this new attribute? (Would we?)

Thank you.

Roenbaeck

First, the naming may be changed. The prefix 'pvpvr' is an acronym for point-in-time version point-in-time version reliability, reflecting the parameters to the function. I think 't' for time, travel, or time-travel is easier to remember.

To your questions, yes, the returned rows when using the function may have null values in them. I would recommend replacing them with a string value, perhaps 'Absent', as well as a graphical hint that distinguishes them from other strings in a user interface. If the database would take care of replacing null with a string, then an application could no longer tell this string apart from other string values, which is why I recommend pushing the null-replacement up to the application layer. That way you can make the replacement red, for example, in order to make it stand out from the rest.

The function should be modified to include the new attribute. This is how the tool generates code today. Existing tables are not touched when you "upgrade" a database, but views and functions are recreated with any new attributes added. To be exact, another left join from the anchor to the new attribute is added, in an identical manner to how the other attributes are joined. The example below has a lower degree of temporality, but gives you the general idea:

------------------------------- [Latest Perspective] ---------------------------------

-- AC_Actor viewed as is (given by the latest available information)

--------------------------------------------------------------------------------------

CREATE VIEW [dbo].[lAC_Actor] WITH SCHEMABINDING AS

SELECT

[AC].AC_ID,

[AC].Metadata_AC,

[AC_NAM].AC_NAM_Actor_Name,

[AC_NAM].AC_NAM_ChangedAt,

[AC_NAM].Metadata_AC_NAM,

[AC_GEN_GEN].GEN_ID AS AC_GEN_GEN_ID,

[AC_GEN_GEN].GEN_Gender AS AC_GEN_GEN_Gender,

[AC_GEN_GEN].Metadata_GEN AS AC_GEN_Metadata_GEN,

[AC_GEN].Metadata_AC_GEN,

[AC_PLV_PLV].PLV_ID AS AC_PLV_PLV_ID,

[AC_PLV_PLV].PLV_ProfessionalLevel AS AC_PLV_PLV_ProfessionalLevel,

[AC_PLV_PLV].Metadata_PLV AS AC_PLV_Metadata_PLV,

[AC_PLV].AC_PLV_ChangedAt,

[AC_PLV].Metadata_AC_PLV

FROM

[dbo].[AC_Actor] [AC]

LEFT JOIN

[dbo].[AC_NAM_Actor_Name] [AC_NAM]

[AC_NAM].AC_NAM_AC_ID = [AC].AC_ID

AND

[AC_NAM].AC_NAM_ChangedAt = (

SELECT

max(sub.AC_NAM_ChangedAt)

FROM

[dbo].[AC_NAM_Actor_Name] sub

WHERE

sub.AC_NAM_AC_ID = [AC].AC_ID

)

LEFT JOIN

[dbo].[AC_GEN_Actor_Gender] [AC_GEN]

[AC_GEN].AC_GEN_AC_ID = [AC].AC_ID

LEFT JOIN

[dbo].[GEN_Gender] [AC_GEN_GEN]

[AC_GEN_GEN].GEN_ID = [AC_GEN].AC_GEN_GEN_ID

LEFT JOIN

[dbo].[AC_PLV_Actor_ProfessionalLevel] [AC_PLV]

[AC_PLV].AC_PLV_AC_ID = [AC].AC_ID

AND

[AC_PLV].AC_PLV_ChangedAt = (

SELECT

max(sub.AC_PLV_ChangedAt)

FROM

[dbo].[AC_PLV_Actor_ProfessionalLevel] sub

WHERE

sub.AC_PLV_AC_ID = [AC].AC_ID

)

LEFT JOIN

[dbo].[PLV_ProfessionalLevel] [AC_PLV_PLV]

[AC_PLV_PLV].PLV_ID = [AC_PLV].AC_PLV_PLV_ID;

Uni-temporal output, like the one above, is handled by the tool today. As well is bi-temporal, which we later will deprecate in favor of concurrent-temporal. We are currently and fervently working on getting concurrent-temporal output from the tool.

cccv

I like the idea of using 't' as the prefix for that time-traveling function.

It makes sense that there may be NULLs in the rows returned by that function. I agree: keep NULL out of the database and push NULL-replacement to the application layer. (Before I encountered AM (and 6NF), I had assumed that I'd always see NULL in the database. What a relief!)

Thanks for your example of multiple attributes returned by that time-traveling function; I get the idea.

I look forward to trying an Anchor Modeler that produces concurrent-temporal SQL DDL! Thanks for your efforts and help.