[setr]

The primary impetus is to enforce the constraint: a billing is either insured or uninsured. Insured has additional metadata X, Y, Z. Uninsured has additional metadata A,B,C. A billing cannot be both.

If you separate them into different insured and uninsured tables, then any tables associated with billing generally needs to be cascaded into an insured/uninsured variants as well. Billing_customer and billing_customer_details now becomes uninsured_billing_customer and insured_billing_customer and uninsured_billing_customer_details, etc.

As you add more data of this constraint, everything fragments again, scaling at 2^n tables. This is similar to the async coloring problem; what you wanted is to locally fragment the data model, but instead anything that touches it gets poisoned.

Ideally the DB would let you enforce the constraint UNIQUE(uninsured.billing_id, insured.billing_id) and split-table would suffice

I’m not seeing any top-level comments that resolve this —- other than ignoring the problem altogether (let the data model encode invalid states, handle it in app code), or switching to a different database.

[frollogaston]

Letting the app code enforce these constraints isn't ignoring the problem, it's how you solve this. Your DB is never going to represent all the business logic by itself. You can also add the slightly clunky constraint it mentions if you really want.

I wouldn't do this with separate tables. I also wouldn't do this with polymorphism, or OOP in general, even if the DBMS properly supported OOP. Trying to represent these constraints by classifying things will get confusing fast.