Your property bag idea is usually called the entity-attribute-value or EAV pattern in database design, and it's notoriously messy. There are some situations where it's called for but it's hard to think of an example where it isn't a compromise with an inherently unpleasant structure.
Attribute per column is effective but leads to really sparse tables and puts major implementation & operational constraints around customizability.
Postgres' JSON/JSONB can do a lot more than you might think. Check out the documentation.
Thanks for the feedback and I fully agree on the bag being horrific over time. Do you believe there would be no case to use a document store dB and a relational dB? I'm seeing lots of good feedback on postgres but wanted to check it's not a case of people knowing that tech more or feeling more comfortable with it.
There are even a few nice mappers fully sold on the postgres route like massive.js which should ease the burden. github.com/dmfay/massive-js/blob/m...
hey, that one looks familiar!
I've split data stores before and losing referential integrity and joinability is annoying enough that I'd only do it if there were pressing performance or data governance concerns. With Postgres on the relational side of the equation, foreign data wrappers might ameliorate some of the ugliness but minimizing the number of information sources is still one of the key parts of keeping your architecture simple.
Lol didn't realise you worked on massive 😂
Do you happen to have any orm recommendations that work with massive.js or is the idea purely to skip all of that and just use massive direct? The documentation makes it clear it's not there to be an orm, but I do love having type security in my app as well as default values etc so if there is a preferred way to get into that I'd love to hear.
There's a subtle but important distinction between "not there to be an O/RM" and "not an O/RM"! Massive fills the same role of helping you get data into and out of Postgres, but it's a different way of approaching the problem: data mapping as opposed to object/relational mapping. The way Massive is organized effectively constitutes an API for your database with tables, views, functions, and scripts as endpoints.
Default values can (and really should no matter what, because the database is the final arbiter of what your data looks like) be specified on columns in your CREATE TABLE scripts.
We're a place where coders share, stay up-to-date and grow their careers.
We strive for transparency and don't collect excess data.