That's a cool approach that could work well if you don't need realtime data validation such as in a UI. I would love to find a solution that allows the same validation rules to be used in the DBMS as well as in the backend and frontend code.
I disagree. From a fact-oriented modeling perspective, relationships among entities are already well-modelled and represented in tables. There's a reason the relational model calls tables relations - they relate things. The mistake here is viewing tables/rows as representing entities, FK constraints as representing relationships, and viewing entities as containers of attributes rather than the subjects of facts.
Joining tables is composing complex facts from simple ones, and is the opposite of normalization which is decomposing complex facts into simpler ones. The ability to join tables on arbitrary conditions is fundamental to the ability to ask a DBMS complex questions and have it respond with all the facts that match that question.
In my view, the O/R impedance mismatch derives from a number of shortcomings. Many developers view entities as containers of their attributes and involved only in binary relationships, rather than the subjects of n-ary facts. They map directly from a conceptual model to a physical model, bypassing logical modeling. They view OOP as a data modeling system, and reinvent network data model databases and navigational code on top of SQL.
In real life, insurance often doesn't pay for everything and service providers would need to support capturing both sets of info, not either one or the other. But that's beside the point.
Properly naming check constraints can go a long way towards providing better error messages, and then there are stored procs which can be used to ensure that multiple tables are updated as a single logical step. It would've been nice if SQL supported literal values in FK constraints, e.g. FOREIGN KEY (id, 'uninsured') REFERENCES patients(id, payment_type), as well as exclusion constraints, e.g. FOREIGN KEY (id) NOT REFERENCES insured_payment_info(id). As it stands, my preferred approach would be combining approach 4 with stored procs, except I would make id the primary key and (id, payment_type) a unique key.
EF is limited to binary relationships: https://learn.microsoft.com/en-us/ef/core/modeling/relations.... When entities are mapped to rows and relationships to foreign key constraints, it results in a network data model interpretation of SQL databases. The relational model is better conceptualized from a fact-oriented modeling perspective, in which entities map to values, domains to FK constraints, relationships to the association of two or more entity sets in a table, and rows represent propositions about entities rather than entities themselves. However, non-binary relationships don't easily translate into navigational data access (cf. Bachman's paper "The programmer as navigator" about the network data model) and a logical view is difficult for the non-mathematical programmer, while structs and pointers are naïvely easy to understand, that's why ORMs remain popular. The impedance mismatch is less about mapping flat to rich data structures than about the network data model vs fact-oriented perspectives and row-at-a-time vs batch data access.
"Object-relational mapper" is a misnomer, they should be called network data model to SQL mappers. They're not primarily for mapping between different arrangements of the same data, but about recreating a network data model perspective. How many ORMs support composite primary keys and non-binary relationships?
As you said, the challenge of understanding math is why the relational vision failed, but also why the industry keeps reviving the network data model.
> you need software to map the relations to objects
If you start with a network data model perspective and build that into your system, then it follows that you'll want a network data model to SQL mapper. That's what ORMs are, and the need for them comes from your approach, not from the tools you use.
There's a different approach - use OOP to build computational abstractions rather than model data. Use it to decompose the solution rather than model the problem. Have objects that talk to the database, exchange sets of facts between it and themselves, and process sets of facts. In the process, you can also start viewing data relationally - as n-ary relations over sets of values - as opposed to binary relationships between tables of records.
Information systems are not domain simulations, simulations compute the future state of the domain whereas information systems derive facts from known facts at the present time.
For a visual metaphor, car engineers don't use roadmaps as design diagrams and they don't model the problem domain in the systems they build. A car isn't built from streets, turns, road signs, traffic lights, etc. And despite that, cars function perfectly well in the problem domain. A car generally doesn't need to be refactored and reassembled when roads or rules change.
Consciousness for me is tied to internal modeling of the world and oneself in it. An agent without an internal model is limited to reacting to immediate inputs, and constrained in terms of the complexity of memory it can have. It could sense pain, for example, it could react based on the location of the pain, it could have thresholds based on experiences of pain. But it could not have a concept of itself being harmed, or objectify the source of the pain, or reason how to minimize the pain. It could only react, with some mix of direction or randomness.
With an internal model of the world, an agent can objectify patterns of experience, view the world as consisting of things, recognize things, associate behavior with and predict things, react based on a more comprehensive understanding of the structure of the world rather than just what it can immediately experience. For example, without looking, I know there's a closed door behind me. I have an internal model of my environment, built from simpler object models such as wall and door and window and floor. I am aware of my circumstances and of my position within it. I can interpret sensory experiences in terms of that model - the sound of the door opening, or a gust of wind on my neck. This is consciousness in my view - experiencing the world in terms of models which divide it into recognizable and predictable parts.
Different agents' modeling abilities can vary, depending on their available memory, pattern recognition abilities, and so on. Also important is the ability to hypothesize - manipulate models not related to immediate experience - and communicate models. Humans tend to be pretty good at both these things.
Free will, for me, is not an inherent ability of an agent, but rather a judgment of an agent's decision-making abilities from the perspective of another agent. If one agent can fully control or predict the behavior of another, the latter has no free will. To the extent that an agent is uncontrollable or unpredictable, it's said to have free will. Therefore I view free will as an arms race between competitive agents. Memory and modeling prowess are the basic weapons in this war, but they demand a high amount of energy. Evolution has equipped conscious creatures with a few other tricks, such as forgetting, which helps to increase unpredictability and keep down energy demands.