What I’m doing right now is putting synthetic (auto incremented) ids on every table, but also adding lots of compound unique indices. We have a lot of long background processes that might need to be restarted in the middle. The natural key definition allows for easy idempotency in subsequent runs (the jobs only upsert with. collisions handled by a simple timestamp update).
Most entities are tied to a version record (more or less a God object). But some span across versions. I’m able to have the entities that aren’t tied to a version float around because they reference the natural key of the versioned entities, minus the version’s synthetic key.
Most entities are tied to a version record (more or less a God object). But some span across versions. I’m able to have the entities that aren’t tied to a version float around because they reference the natural key of the versioned entities, minus the version’s synthetic key.