3 ms·
Without specific support from the RDBMS, bitemporal schemas are difficult with regard to cross-table references, such as foreign keys. Rows that need to be cons
by layer8 2y ago
Without specific support from the RDBMS, bitemporal schemas are difficult with regard to cross-table references, such as foreign keys. Rows that need to be consistent between tables aren’t necessarily 1:1 anymore, but instead each row in one table needs to be consistent with all corresponding rows in the other table having an intersecting time interval. You then run into problems with transaction isolation and visibility.
- hobs 2y agoPretty much, you want triggers to store things in a schemaless fashion in an audit format so that you are free to migrate tables. This does require either knowing the schema at the point in time or recording enough information to do a schema on read. The other options are of course you basically run a table like an API, always adding, never removing.
- pyrale 2y ago> bitemporal schemas are difficult with regard to cross-table references Who needs more than one table ? >:) More complex models can be built and stored separately. The great benefit of this method being that, once you're unhappy with your table model, you can trash it and rebuild it from scratch without regard for data migration.
- layer8 2y agoYour last sentence sounds more like event sourcing than bitemporal databases, which are quite different concepts. I don’t see how bitemporal schemas simplify schema migration.
- pyrale 2y ago> I don’t see how bitemporal schemas simplify schema migration. It's not the bitemporality that helps, it's primary data immutability. The event sourcing community has its own specificities (event sourcing advocates saving decisions, not outside data), but not on that aspect: if you store events immutably as this article describes, you are bound to benefit from read models that you can trash and rebuild at will.