Historizing a tie for 1:1 relationship between anchors

Anonymous

Hello.

This question is regarding entity sub-types and the issue of historization. I will use a restaurant example to illustrate the issue.

Let's say in a traditional database model I have a table for Person. It has fields/attributes common for a generic person like StreetAddress, City, Email, Phone, etc.

Now say that I want to model sub-types of the Person entity by creating two new tables - Employee and Guest that should both reference the Person table through a foreign key. An Employee will have a SocialSecurityNumber field, and a guest may have a Table or SeatNumber attribute. These attributes are specific to their respective entities and should not be included as null-able columns in the person table (which would not be legal in Anchor modeling anyway). At the same time, because we want to preserve data integrity across the domain, we want to keep a person's PhoneNumber, address, etc. in the generic Person table one time rather than replicating this information multiple times in these sub-tables so that we don't have update anomalies.

My question pertains to historizing a tie in a one-to-one relationship between anchors/entities. If I do this using the above example, I get the error message: "The tie PE_of_EM_is has 2 roles outside the identifier, which is not recommended for a historized tie. In order to determine what changes over time leaving exactly one role outside the identifier is recommended." When trying to generate and execute the SQL script, there were a few compilation errors such as blank WHERE clauses, etc. If I correct the syntax errors, I can get the script to run, but the problem is not solved.

Can you please explain why this is not allowed for one-to-one relationships? Why can the problem not be solved for including both anchor IDs in the primary key for the tie table? It seems that logically this should be legal. I understand the concept of immutable surrogate IDs in the anchors to identify an entity, but within the tie, should we not be able to show that an employee has a relationship with the person entity, but has come in and out of scope possibly several times when hired, fired, rehired, etc.?

Roenbaeck

Hi Adam,

First, the blank WHERE clauses should not happen, even if you do something that is not recommended. I will try to fix this bug as soon as possible.

The technical reason we discourage historized all-key ties in general is that it allows for any part of the key to change over time and the whole notion of a key becomes shaky. In an all-minus-1-key tie, on the other hand, it must be the role outside of the key that changes over time and the notion of a key remains pristine.

Looking at your example and specifically "come in and out of scope" for the relationship, I see this as a three-way tie: PE_of_EM_is_SCO_with. In this historized tie the reference to the knot SCO_Scope is not part of the primary key. That way, over time, the scope may change between 'hired', 'fired', 'rehired' unambiguously for the key. This is contrary to a historized binary tie PE_of_EM_is, in which you cannot determine the semantical difference between the rows <1,1,2012-01-01> and <1,1,2012-12-12>.

I hope that answers your question?

Anonymous

Roenbaeck,

Thanks for the speedy reply. On a side note I will add that I am a big fan of the research of Snodgrass on bitemporal databases and I understand the complexities of this type of temporal model, and I must say that yours is the best solution for this I have seen to date. Hats off to you.

About the solution you proposed, what you are saying logically makes sense to me. However, I couldn't get it to work properly with the designer:

I created two anchors - Person and Employee. As stated previously they have a one-to-one relationship through a tie that I desire to historize because it can change over time through termination and rehiring of employment. (All employees are of the person table, but not all people are employees. Employees have additional sensitive data that a generic person does not have.)

From this tie, I added a knot called Expired, similar to your recommendation. The endpoint connecting this tie to the knot is in the "many" state, while the other two endpoints linking Person to the tie and Employee to the tie are in the "one" state.

After these constructs are in place I historize the tie. When doing so I still get the same error message within the designer. When generating the SQL script though, there are no compilation errors, though I am not sure this is giving me what I want. The tie linking these three objects is called 'PE_of_EM_is_EXP_until', and it has the primary key ('EXP_ID_until', 'PE_of_EM_is_EXP_until_ChangedAt'). The Person ID 'PE_ID_of' and the Employee ID 'EM_ID_is' are referenced through foreign keys but are not part of the primary key. Correct me if I'm wrong, but if I am understanding it right, in this case there can only be a single instance of a datestamp and the Expired flag.

For example, say we have Employee 1 and Employee 2. In the knot Expired, 0 = false, and 1 = true. So if i wanted to denote in the database that both of these employees are terminated on June-20-2012, I couldn't do it because the primary key would not allow two rows with the same values of (6/20/2012, 1) and (6/20/2012, 1), even though their foreign keys reference different anchor IDs.

Can you please clarify?

Roenbaeck

Adam,

The good new is that I fixed the bug with the empty WHERE clauses, but I haven't made the change available online yet. Also, cardinalities in Anchor Modeling are set perhaps differently than you expect. You set them non-temporally, i.e. as you would like them to be at any given point in time. So, in your example that would be 1-1-1 for the tertiary tie.

Now for the bad news. While fixing the bug with the WHERE clauses I discovered that the current constraints on a tertiary 1-1-1 tie are not enough to prevent inconsistent data from being entered. The following can be inserted without errors:

1, 1, 1, 2001

1, 1, 0, 2002

2, 2, 1, 2001

1, 2, 1, 2003

The final row is dubious. The ID = 2 is already spoken for by the previous row. I will need to give this some more thought. It may be solved automatically when I add support for preventing restatements in ties (currently only for attributes). In the meantime I hope being aware of the problem is enough.

Anonymous

Roenbaeck,

Thanks again for the reply and clarification. This is helpful to know that I should have all endpoints set to the 'one' state on the tertiary tie and also that this erroneous condition exists.

I had an idea that I will share regarding the dubious row that allows different person entities to be associated with an employee over time, as you have outlined. I'm not sure how it will fit your current data model, but I thought it could be worth mentioning:

In this scenario (a historized one to one relationship,) instead of a tie table having foreign keys that map directly to both IDs in both the person anchor and employee anchor, have an intermediary table that consists of the following:

-PersonID - PK/FK

-EmployeeID -FK

-a unique constraint on EmployeeID

-no associated historization data / ChangedAt field in this table

This table would function like an anchor in that it would identify a one to one relationship over all of time, but would consist of two ID columns.

Then you could have a tie table that contains the exact same fields that you have with the current model. However, instead of referencing the anchor IDs of PersonID and EmployeeID directly as you do in the current model, map the tie table to this intermediary table because it only allows a single personID to be associated with one EmployeeID. Then this tie table can have all of the same constructs as the present model does, but without violating this condition. It would also be able to include the historization data and data from any associated knots without consequence.

I haven't tested this scenario, but I just thought it might be worth considering or looking into. This is just me thinking out loud digitally.

Anonymous

Correction:

In the intermediary table as described above, the keys could be as follows:

PK: (PersonID, EmployeeID)

Unique constraint on PersonID

Unique constraint on EmployeeID

Roenbaeck

I don't know if you are still following this old thread, but the way one-to-one-...-one ties are handled has been rewritten in the test version, and hopefully this will sort out the problems we discussed above. I have only tried a few basic tests, so there may still be bugs present, which is why I am wondering if you have any of your models still available and can test if the tool produces executable and well behaving SQL?

The test version is available here:

http://www.anchormodeling.com/modeler/test