Effective dating of relationship changes #376
Description
Effective dating is a concept frequently used in ERP and other business solutions where every piece of data in the database needs to have a date associated with it, and old data is never deleted so that is always possible to know what the data looked like in the past. It might have other names.
https://help.sap.com/docs/successfactors-platform/sap-successfactors-api-reference-guide-odata-v2/effective-dating is an example.
For ordinary tables, this can be achieved in butane currently by having UUIDs as primary keys, and putting the real identifiers in a separate field so that many rows can have the same real identifier, using logical deletes, and column updates are achieved by creating new rows.
That also can be used for ForeignKey.
However, for Many relationships, this is harder to do with butane.
I think to support effective dating for many relationships, we would want a new type, or make Many a more complex that optionally supports effective dating.
The "many with effective dating" would include timestamp columns in the relationship, and then only performs logical updates. e.g.
- "add" also puts the current timestamp in the "start" field for the row.
- "remove" puts the current timestamp in the row's "end" field of the row.
I suspect we would want a new type for this, as this functionality would need the "datetime" feature, and Many would likely get messy if some extra optional functionality required a feature.