Even "hard guarantees" are worthless. All it takes is one client with a checkbook to change business requirements.

I agree that this is an issue, but I think the answer is simply using a very flexible basic data representation (which admits invalid state) and then using predicates to refine it. e.g. starting with a list of (start,end) intervals and then adding predicates for valid intervals (start <= end), ordered, non-overlapping and continuous.

If any of the requirements change, it's easy to either add more predicates or relax/even outright remove them.

If you play your cards right, you can even get your type system to alert you to every place in your code that needs to change when you change the constraints.

I don't deny there's a certain art to that, and I can't explain it all myself. But I don't even necessarily mean amazing clever type tricks like you might see in Haskell or something, I mean that something as simple as "I've changed the definition of what a 'Customer' is in some fundamental manner, so I'm going to rename that class to 'CustomerNew', use the compiler to point me at every single place that breaks, audit it, change the local name to CustomerNew, and then, once everything is fixed, use my IDE's rename feature to rename CustomerNew back to Customer before my final commit". Many times you can get by just by renaming a field or something to similar effect, but in the worst case you may need to audit everything.

It's one of the more tedious bits of the job sometimes, but net-net this can still be a timesaver, if you account for the full cost of the trickle of bugs this sort of thing can prevent.

You could always split your types into frontend and backend types where the backend ones are more open and the frontend ones are more restricted. I don't necessarily mean FE/BE as on the web. A lot of code is only interested in shuffling around data anyway, the shape is fairly uninteresting.

I would put it the other way around: make your basic representation restricted, but present a more permissive API. This way, the data model helps enforce your constraints, but you don't need to redesign the API when the requirements (inevitably) change.

If you make your front-end interface more complex than is needed to represent current data, you may save clients a migration in the future. But you force your clients to deal with the complexity right now, and you could wind up needing to change things in a different way in the future. Neither of these concerns clearly dominates all the time - it's a question of just how much effort you're saving them, just how likely various changes are, etc.

On the other hand, if you make your internal representation support more complexity than what you hand out to your users, there arises the question of how you simplify (probably lossily!) or break when you wind up with data that can't be expressed with the simpler interface. But you might well be saving yourself a costly data migration down the line. Also a decision with trade-offs that could potentially go either way.

Kind of like internal data/algorithms and external interface in OOP?

That could be fixed with 1 additional concept. Instead of

    Customer: 1 - 1: Set(Dates)

What if it was

    Customer 1 - *: Contract: 1 - 1: Set(Dates)

Overlap achieved.

Additionally, turning (startDate, endDate) into a set of dates will make the code more complex in some places. Before:

    SELECT event FROM events WHERE endDate<"2021"

After:

    Whatever additional complexity you
    add to your codebase to query end
    dates.

It's not hugely complex although definitely more than the first example. I guess it depends whether it's offset by the benefits...

https://dbfiddle.uk/?rdbms=postgres_11&fiddle=50e6a963cd1db0...

(YMMV, obvs.)

It's not that much more complex: if you do

    SELECT event FROM events WHERE startDate < "2021"

then all but one of the results (the one with the greatest `startDate`) will also have an implicit end-date prior to 2021.

This is fine for an open-ended query like the one given, because you still receive all the relevant data. But if you're looking at a range, for the same reason you have one extra at the end, you also have one missing at the beginning. And you can't just filter away missing data.

Doing all the logic in SQL requires more complexity using subqueries.

It gets uglier if you need to find the contract valid on a certain date based off of a join.

These issues can be covered up with code as it will a easier to have reusable functions, but it makes the job of a data analysts much more difficult and error prone.

Uglier than having to find the record after the one you're inserting (so you can determine your new record's end date from the subsequent record's start date) and the record before (so you can modify its end date to match your new record's start date)?

Until business tells you that endDate is not necessarily greater than startDate. <- real world experience

Sounds interesting can you explain more about it?

I think you are vastly overstating the risks of changing requirements. It is usually easy to go from a less permissive model to a more permissive one, but the opposite is often difficult.