Comment by Spivak | Hacker News Reader

Comment by Spivak | Hacker News Reader

I think there's a huge difference between how you design:

1. A database that's meant to be understood by programmers, make queries by the application efficient in space and time, and provide strong referential integrity.

2. A database that's meant to be played with by humans, where duplication is fine, referential integrity is a nice-to-have, every column is okay to be nullable, tables should contain complete objects as understood by the user that map 1-1ish to the real world, that eliminate as many opaque ids as feasible, and foreign keys might exist but aren't enforced in any direction.

The latter database is far more ergonomic and you won't run up against a user frustratingly bashing their keyboard because the database just refuses to do what they want. The stakes in #2 style databases are extremely low-- let the user destroy their copy of the database it's fine, we can reload from the last save.

The nice thing is that it seems very possible to go from #1 -> #2 mechanically, and hand that off to the users who want to play with it.

Some technical design elements can enable this style of non-programmer usage that most databases engines do not currently support.

First, treat each column separately in the physical engine (column store). Users ought to never need to worry about column count limits, row byte limits, or sparsity. Similarly, schema operators such as adding, removing, or reordering columns ought to always be instant atomic changes.

Imagine how much faster it would be possible to explore the schema design space if changes were instant and didn’t require “migration scripts” or data copies from an old table to a new one. It would make the database feel more like a spreadsheet!

Next, there ought to be Git-style forking and merging so that creating a test environment should also be a lightweight operation just like creating a local branch in a Git repo. Merging changes can be either schema only or schema+data -- the latter to support slowly-changing “master data” editing workflows. Currently, few if any database support the equivalent of a pull request with reviews to merge data. Hence the excessively complex access controls that could all be replaced with a single "peer review" operation.

I’ve seen a fancy category-theoretic approach where forks of the database can receive live data updates from production. This would allow UAT and similar environments to be evergreen with zero infrastructure code such as nightly ETL sync jobs.

Many operational tasks can be eliminated by tying live environments of both the code and data to Git branches of a single (mono) repo. Now the code and data schema can’t go out of sync! Literally impossible. No need for scaffolding, or ORMs, or any of those layers of overcomplicted abstractions! The schema is just “there”, in the repo, always representing reality.

Sprinkle on a Microsoft Access style form designer but with HTML5 and guest user support and you have a billion dollar product.

The 4GL dream hinges on figuring out how to transition from #2 -> #1 seamlessly. The spectrum is wider and more complex than "Excel -> Postgres" but even solving that journey would be a good start. It could save us all from a whole a bunch of ~needless engineering work.

> it seems very possible to go from #1 -> #2 mechanically, and hand that off to the users who want to play with it

NocoDB seems like a reasonable attempt of taking this approach for Postgres.

> The 4GL dream hinges on figuring out how to transition from #2 -> #1 seamlessly

I guarantee you there is no algorithmic way to do this. Going from "big flat file" to "nicely organized 3+NF schema" adds a lot of underlying domain knowledge and information to the system. You're asking for "enhance!" on the database schema. It will always require expert understanding.

Reply on news.ycombinator.com