Your second optionIf you’re using a database for storing your objects this is preferablethe preferred option:
Make your entity E(key, attribute 1, attribute 2, ... attribute N, valid_start, valid_end) and manipulate the valid_... columns to show when the respective state was stored or replaced by some other state.
This is preferable, because:
a) you can handle deletion and reinsertion of records easily (like an employee leaving the companya contract becoming inactive and then being reactivated, or a service deleted from a contract and then being reestablished some time later rejoining).
b) the theory of that is well developed: See „Developing Time-Oriented Database Applications in SQL" by Richard T. Snodgrass, 1999, available from his homepage, which considers primary keys, uniqueness conditions, referential integrity, joining temporal tables and coalescing (selecting fewer columns in a temporal table and adjusting the time fields such, that only single records for each state are reported, ignoring changes in the other columns). It even deals with practicalities of the approach in the SQL databases of that era (providing code samples). See also Temporal Databases.
c) I have seen this in the wild in business critical deployments.
d) I have used it myself in a productive data warehouse.
The only drawback is, that things can become slow, if the rate of change is high.
Your second option is preferable:
Make your entity E(key, attribute 1, attribute 2, ... attribute N, valid_start, valid_end) and manipulate the valid_... columns to show when the respective state was stored or replaced by some other state.
This is preferable, because:
a) you can handle deletion and reinsertion of records easily (like an employee leaving the company and later rejoining).
b) the theory of that is well developed: See „Developing Time-Oriented Database Applications in SQL" by Richard T. Snodgrass, 1999, available from his homepage, which considers primary keys, uniqueness conditions, referential integrity, joining temporal tables and coalescing (selecting fewer columns in a temporal table and adjusting the time fields such, that only single records for each state are reported, ignoring changes in the other columns). It even deals with practicalities of the approach in the SQL databases of that era (providing code samples). See also Temporal Databases.
c) I have seen this in the wild in business critical deployments.
d) I have used it myself in a productive data warehouse.
The only drawback is, that things can become slow, if the rate of change is high.
If you’re using a database for storing your objects this is the preferred option:
Make your entity E(key, attribute 1, attribute 2, ... attribute N, valid_start, valid_end) and manipulate the valid_... columns to show when the respective state was stored or replaced by some other state.
This is preferable, because:
a) you can handle deletion and reinsertion of records easily (like a contract becoming inactive and then being reactivated, or a service deleted from a contract and then being reestablished some time later).
b) the theory of that is well developed: See „Developing Time-Oriented Database Applications in SQL" by Richard T. Snodgrass, 1999, available from his homepage, which considers primary keys, uniqueness conditions, referential integrity, joining temporal tables and coalescing (selecting fewer columns in a temporal table and adjusting the time fields such, that only single records for each state are reported, ignoring changes in the other columns). It even deals with practicalities of the approach in the SQL databases of that era (providing code samples). See also Temporal Databases.
c) I have seen this in the wild in business critical deployments.
d) I have used it myself in a productive data warehouse.
The only drawback is, that things can become slow, if the rate of change is high.
Your second option is preferable:
Make your entity E(key, attribute 1, attribute 2, ... attribute N, valid_start, valid_end) and manipulate the valid_... columns to show when the respective state was stored or replaced by some other state.
This is preferable, because:
a) you can handle deletion and reinsertion of records easily (like an employee leaving the company and later rejoining).
b) the theory of that is well developed: See „Developing Time-Oriented Database Applications in SQL" by Richard T. Snodgrass, 1999, available from his homepage, which considers primary keys, uniqueness conditions, referential integrity, joining temporal tables and coalescing (selecting fewer columns in a temporal table and adjusting the time fields such, that only single records for each state are reported, ignoring changes in the other columns). It even deals with practicalities of the approach in the SQL databases of that era (providing code samples). See also Temporal Databases.
c) I have seen this in the wild in business critical deployments.
d) I have used it myself in a productive data warehouse.
The only drawback is, that things can become slow, if the rate of change is high.