I am creating a time series database that follows the table structure here: Storing intervals of time with no overlaps. The difference with my design is that the
FinishedAt column changes every time a user updates that particular record (you can actually see my question on his site at the bottom).
Basically, I have a
FinishedAt column that has the current record with a date of
9999-12-31 until the next record with the same business key gets updated (in his example that key would be the
SettingID). I run into the problem with his
FK_IntegerSettings_SettingID_PreviousFinishedAt since I cannot make
FinishedAt part of my primary key. I cannot make it part of my primary key because it is always changing....but is it okay to make
FinishedAt part of my primary key, which would involve updating the primary key? Is this frowned upon even in a time series table?
I realize I can still use his other constraints, but I loose the very important
foreign key constraint. This is my first encounter with this type of design so any direction will be appreciated. Thank you.