I think you have a point, and SCD type 2 feels like a workaround, but there is also something to be said for the ability to query every row as it was at any given version.
I’m not saying that SCD type 2 is the best solution given there might be a more domain-specific way to do it, but I see it a lot like file-based version control.
It’s convenient to be able to examine all files as they existed at any point in time, without having to “model” the ways in which those files might change directly into the domain of the individual files.
If you have something like dolt (not affiliated), a version controlled database, you wouldn’t have to slap change dates on anything OR create your historical table. The changes would be implicit in the version history.
If you have something like dolt (not affiliated), a version controlled database, you wouldn’t have to slap change dates on anything OR create your historical table. The changes would be implicit in the version history.