You'd need to handle it in your code. The last thing I want is a database that returns different columns per row.
No, these aren't SQL limitations, it's design that is super complex. Figuring out how to index over these multi-type fields isn't a SQL limitation, it's a hard engineering problem.
> The last thing I want is a database that returns different columns per row.
Actually that's exactly what you'd want, because it saves you from running two different queries in those cases with properly normalized disjoint data sets, and moves more of the domain's constraints into the database schema where it belongs.
Definitely not what I want. I'm perfectly happy getting both fields at the same time in a single query the way I do now.
> and moves more of the domain's constraints into the database schema where it belongs.
No, I prefer to keep my domain constraints at the application level where they're far more flexible.
The database is for storing information, not for validating my business logic.
I mean, I realize some people want to build some of that logic into the database, especially when many applications interact with it. But it's not a superior design pattern. If you have a single application, it's perfectly valid and desirable to put all business logic in the application, not the database.
No, these aren't SQL limitations, it's design that is super complex. Figuring out how to index over these multi-type fields isn't a SQL limitation, it's a hard engineering problem.