While this is great if you know exactly what you want to achieve, it does “lock you in” those constraints on a more fundamental level. More times than I can count I’ve seen business requirements change to require those “unrepresentable” states, and since you’ve now designed you whole data model around it you need to add awful hacks to make it work.
The timeline example is actually very telling. A lot of times you’d actually want to encode overlapping time periods at the edges.
You’d be laughed out off a meeting where a business asks about this and you smugly explain how it is unrepresentable.
I guess what I’m saying is that it might be worth over designing your system a bit to leave you some wiggle room, unless you have hard guarantees that something should be “impossible”
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.
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)?
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.
The timeline example is actually very telling. A lot of times you’d actually want to encode overlapping time periods at the edges.
You’d be laughed out off a meeting where a business asks about this and you smugly explain how it is unrepresentable.
I guess what I’m saying is that it might be worth over designing your system a bit to leave you some wiggle room, unless you have hard guarantees that something should be “impossible”