I am new to Anchor Modeling, and I have a very positive impression of it. I am considering creating an auditable concurrent-temporal anchor database to replace a legacy, "3NF" database. I want to track both concurrent changes to the domain and corrections to concurrent changes to the domain by multiple users; so, by "concurrent-temporal", I mean both "concurrent" and "bi-temporal", and I apologize if I've abused the term "concurrent-temporal".
While trying to understand Anchor Modeling with respect to my project, I encountered Lars Rönnbäck's (@roenbaeck's) 'Back to the Moment' slideshow. I have questions about slide/page 33:
0. Does "UQ" abbreviate "unique" and constrain a so-annotated column's value to be unique across all the table's rows?
1. About column MU_NAM_Reliable of table MU_NAM_Museum_Name_Annex:
1.0. Does that column have a truth value type (for example, "boolean" or "bit")?
1.1. Does "(calculated)" mean that the inserting code calculates the truth value (probably with respect to the "MU_NAM_Reliability" value)?
1.2. What benefit does this column provide? Does it exist to simplify queries of only "reliable" posits?
2. The model is mono-temporal, not bi-temporal, correct?
3. What does column PA_hanging_MU_at_ChangedAt in table PA_hanging_MU_at_Posit historize?
You've not abused the term "concurrent-temporal", which in fact is short for "concurrent-reliance-bi-temporal". We shortened it to be less of a tongue wrestling exercise.
I'll answer your questions:
0. UQ is short for unique, yes.
1.0. Yes, MU_NAM_Reliable has a truth value, and I would have used 'bit' in an actual implementation if it wasn't for the fact that SQL Server has problems with column statistics for bit type columns, so we use a tinyint in practice, never holding any other value than 0 or 1.
1.1. Yes, MU_NAM_Reliable is calculated from MU_NAM_Reliability, which may be any data type you desire that has enough granularity for you to capture different degrees of reliability. The logic for the calculation is that anything above a certain value of MU_NAM_Reliability is considered "reliable" information, and anything below that value is "unreliable". In the simplest case you need no degrees of reliability and only want to capture correct and incorrect information. In other words, reliable = 0 means that you have 'deleted' that piece of information. Reliable = 1 means it is correct and not deleted.
1.2. MU_NAM_Reliable is needed in order to maintain integrity over time. Information is 'partitioned' (in a sense) into reliable and unreliable partitions. Each of these two must maintain integrity. We create an assembled view of the posit and the annex, and on this view we add a constraint (primary key) that includes MU_NAM_Reliable and ensures integrity.
2. The model is bi-temporal, which cannot really be seen on the slide. What makes it bitemporal is that for attributes that are historized both the changedAt and positedAt columns are part of the primary key in the assembled view.
3. The changedAt column indicates that this tie is historized, meaning that the relationship that it is describing may change over time, such that different relationships have been valid at different points in time if you look at is as a timeline. Attributes may also have changedAt columns. If they do not, they are static attributes. An example of a static attribute is 'country of birth', which is expected to remain the same, but may be corrected, if spelled wrong for example. An example of a historized attribute is 'marital status', since its value may change naturally over time, such as from 'single' to 'married' to 'divorced'. This too may be corrected, if for example you got the date wrong when someone was married, or if it was actually a divorce and not a marriage, or again if you spelled something wrong.
I hope that answers your questions. Please note that our tool does not generate concurrent-temporal code yet. We are working on it an expect it to be finished after the summer. There is an example of what the output will be like here though: http://pastebin.com/CkEeTPSc
Re: Questions about 'Back to the Moment' slideshow
Thanks -- you did answer my questions. The concurrent-temporal example really helped me, too.
I have more questions:
4. Again in reference to slide/page 33:
4.0. It appears that at most one museum name can be posited across all time. Should MU_NAM_Museum_Name form a uniqueness constraint (UQ) with column MU_ID for table MU_NAM_Museum_Name_Posit? (If not, then why not?)
4.1. For table PA_hanging_MU_at_Posit, do columns PA_ID_hanging and PA_hanging_MU_at_ChangedAt form (a.) a single compound uniqueness constraint, or (b.) two separate simple uniqueness constraints?
4.2. If the answer to question 4.2. is "(a.)", then should column MU_ID_at also participate in that constraint? (If not, then why not?)
4.3. Do I infer correctly that the model should include (but the slide couldn't esthetically include) an Annex table for PA_hanging_MU_at_Posit?
5. Generalizing my questions 4.1. and 4.2., should all non-PK columns in Posit table always participate in a compound uniqueness constraint for that table?
Re: Questions about 'Back to the Moment' slideshow
Thanks for more interesting questions. Here are some answers:
4.0 Yes, it should, it was left out of the slide by mistake :( Every posit should have a uniqueness constraint containing the ID column, the value column(s), and when applicable the changing time column.
4.1 It is a single compound uniqueness constraint, and it appears I was a bit sloppy when I put this slide together, and made yet another mistake :(
4.2 It should, of course, have been a part of the constraint. What can be noted is that what was previously a primary key for the tie (in the uni-temporal model) to ensure the cardinality constraint is now deferred to the assembled view's primary key instead. It is only in that view we can ensure the cardinality constraint within the range of each positor. The uniqueness constraint is there to prevent duplicates of identical posits.
4.3 Yes, there is an Annex table for every Posit table. Only attributes and ties are split up into posits and annexes. Anchors and knots are immutable and eternal and therefor do not need that type of construction.
5. Yes, if a single value differs in any of the non-PK columns, then it should be considered as a different posit.
6. wrp = with respect to
And, please forgive any inconsistencies in naming between the slides and the example I linked earlier. The example is quite old by now and we may have changed terminology slightly as we've been aligning this with our research. If you are interested, there is also a page showing a use-case for concurrent-temporal modeling here:
It is an example taken from a customer lifetime value model, which we based on concurrent-temporal modeling in order to keep track of the 600 different parameters that govern the calculations. Inflation is just a single one of those 600.