Comment by noduerme

>> an object going through a number of states with state transitions was general and much better than the 15+ date columns

It amazes me that anyone would write a schema with a dozen date columns, but I've seen such things. Once you're dealing with a large database of something like that, it's tempting to just add one more column rather than pitch why you should refactor the whole thing.

I try to keep my schemas relational as possible. Actions and immutable records should obviously be stored in separate tables from the objects they reference. However, this does make the UX form design / CRUD process less amenable to simple solutions where forms are just generated right out of a schema.

Your phone number example highlights this. In general of course you want to store contacts in a separate table from customers. But that means you're probably going to need a separate contacts sub-form within your customer form, rather than just inline phone fields. Then form will need to be required or inlined for any new customer before the main customer form can be saved. Stuff like that.

Over the last 15 years or so I've built and refined my own form generator, really a DSL for designing forms that fit this type of thing. In its more basic use cases, each form lives as a row in a database, with each form_item linked to it in a separate table. The form runs a pre-fab query that expects N inputs that it binds to :variables (usually in WHERE or HAVING clauses) and then renders a pre-populated visual form with various input types like dropdowns, checkboxes, calendars, etc (based on those form_items, which can all be styled, required/not required, required based on other answers, etc). Each form_item has its own standard validation or custom validation function on both the client and server side. The form knows which table it wants to write to and which bound variable is the id field it's going to target for update. Sending it a blank id field renders the form with nothing populated and then does an insert instead of an update when it's submitted. It's very slim, about 500 LOC of Typescript and 700 LOC of PHP, including most standard kinds of validations on both ends. I've always toyed with the idea of releasing it it for people to use, but here's the rub: If you want to do anything involving writing to more than one table, you need to write a custom final function for those additional insert/updates.

So, it's a lovely system, but someone coming to it naively would run the risk of designing schemas that were not expressive enough, to try to keep the CRUD system happy. And I think this is just an inevitable problem with all low-code solutions: They don't handle multi-dimensional data the way you want a clean schema to handle it. (And neither do users).