4 ms·

I'll play devils advocate. In any real world system I've worked there are always entities that are purely self contained, but are represented by many different

I'll play devils advocate. In any real world system I've worked there are always entities that are purely self contained, but are represented by many different database tables. When they're saved, the entity is decomposed into its constituent parts and written to those tables. When they're retrieved, those tables are joined together to re-create the entity we care about. And that's all that ever happens, it's just serialised and de-serialised. Isn't it premature optimisation to normalise that entity right out of the gate? Why not stuff it in a JSONB column. When - and only when - you find yourself needing to dig inside of it for queries in other parts of the system, then you normalize it.

clhodapp 9y ago
One word: constraints. The problem with actually storing things in a document-oriented manner is that it requires extreme vigilance and no mistakes to actually keep the shape of your documents consistent. Further, it can be extremely costly to discover all the shapes of data you actually have if you initially didn't treat it as important. More often than not, it is useful to use tools that guide you into being disciplined along the way.
- HumanDrivenDev 9y ago
  Yes, the issue you run into is that then your server has to make sure your json is the right shape. CouchDB actually solves that problem really well IMO. You upload a validation function in javascript to the database that will validate every document that comes in. I wonder if the same thing can't be done with trigger functions in postgres.
  - saltcured 9y ago
    You don't even need to use triggers. You can just write a plain old CHECK constraint on the column storing the document and use a SQL expression with the built-in JSON/JSONB operators or a stored function to interrogate the document how ever you'd like, evaluating to a true value to accept the data or false to reject.
    - ris 9y ago
      You can but complex datatypes (json) have complex (and often ugly) constraint definitions. It's not much fun compared to "look, I just want this field to be a date."
- scarface74 9y ago
  I use C# with Mongo. When I get a Collection<T>, the only thing I can put in the collection is a strongly type collection and my Linq queries are strongly typed. The compiler enforces consistency.
  - ris 9y ago
    Right, but can you ensure that the only thing that ever connects to your datastore (even to perform auxiliary/admin actions or quick data tweaks) is using that code in strongly typed mode? Otherwise you can't be sure what's in your data.
    - scarface74 9y ago
      I can't be be sure that a developer doesn't go behind the scenes and change Sql server either. But programmatically, i do ensure only one microservice (out of process) or module (in process) is writing to a collection. When I'm using an RDMS and developing a system, I also ensure that only one module is writing to a subset of tables that make up an aggregate root. I would never have a system where a bunch of programs are writing to the same tables willy nilly without going through a common interface.
      - ris 9y ago
        > I can't be be sure that a developer doesn't go behind the scenes and change Sql server either. You basically can. The difference is that it's quite obviously notable to a developer of any level that changing the schema in the database is something that should be done with some forethought and care. And then when that change is made, all applications accessing the database get the same new view of the schema so you're not going to have two different clients with a different idea about the schema trying to operate on the database at once because schema is global. Making a change that suuuuubtly alters the way data is stored in an unstructured object is the kind of thing that's really easy to overlook in a pull request. Your "only one module" rule is laudable, but you've also got to make sure that module can do everything you'd ever want to do to the database, including all the "one off" data mangling admin tasks anyone occasionally has to do. Otherwise it will get bypassed. Case in point I used to use an ORM heavily, but it wasn't possible to express everything in that ORM, so where that broke down we had to resort to manual sql. Add a few developers to the equation and you don't know exactly the format of your data. Good schema management practise also means you end up defining all of your schema mutating operations as migrations, leaving you with a rather vital log of how things have changed over time and a good pinch-point to catch inadvisable changes to schema.
ris 9y ago
Yup, and that's exactly the game I used to play. But when you have more than a couple of developers on a project and have ... mixed ... levels of experience, things easily get out of hand while your back is turned. Before you know it you've got a dataset with fields that are... well, you don't know what they are - they could be strings, they could be true, false, entire sub-objects or arrays - they could even be json-null. And then you have to potentially cope with all those possibilities in every place you use that field. Yes, of course you can attempt to put some sort of constraint on the data format (jsonschema perhaps?) but that's not exactly easy to perform as an in-database constraint which is the most useful and powerful form of guarantee you can have. Of course, it also leads less experienced developers away from using postgres' rich types (even datetimes are too rich for json/jsonb!) and leaving it impractical (or impossible) to perform logic on data in-query (which is often the most efficient and robust way of doing it). This road also leads to people trying to make references to other objects within json blobs. Try ensuring referential integrity on that! It all comes down to: you have a schema, whether you think you do or not, and if you let the database know about that schema it can help you manage it.

clhodapp 9y ago

One word: constraints. The problem with actually storing things in a document-oriented manner is that it requires extreme vigilance and no mistakes to actually keep the shape of your documents consistent. Further, it can be extremely costly to discover all the shapes of data you actually have if you initially didn't treat it as important. More often than not, it is useful to use tools that guide you into being disciplined along the way.

HumanDrivenDev 9y ago
Yes, the issue you run into is that then your server has to make sure your json is the right shape. CouchDB actually solves that problem really well IMO. You upload a validation function in javascript to the database that will validate every document that comes in. I wonder if the same thing can't be done with trigger functions in postgres.
- saltcured 9y ago
  You don't even need to use triggers. You can just write a plain old CHECK constraint on the column storing the document and use a SQL expression with the built-in JSON/JSONB operators or a stored function to interrogate the document how ever you'd like, evaluating to a true value to accept the data or false to reject.
  - ris 9y ago
    You can but complex datatypes (json) have complex (and often ugly) constraint definitions. It's not much fun compared to "look, I just want this field to be a date."
scarface74 9y ago
I use C# with Mongo. When I get a Collection<T>, the only thing I can put in the collection is a strongly type collection and my Linq queries are strongly typed. The compiler enforces consistency.
- ris 9y ago
  Right, but can you ensure that the only thing that ever connects to your datastore (even to perform auxiliary/admin actions or quick data tweaks) is using that code in strongly typed mode? Otherwise you can't be sure what's in your data.
  - scarface74 9y ago
    I can't be be sure that a developer doesn't go behind the scenes and change Sql server either. But programmatically, i do ensure only one microservice (out of process) or module (in process) is writing to a collection. When I'm using an RDMS and developing a system, I also ensure that only one module is writing to a subset of tables that make up an aggregate root. I would never have a system where a bunch of programs are writing to the same tables willy nilly without going through a common interface.
    - ris 9y ago
      > I can't be be sure that a developer doesn't go behind the scenes and change Sql server either. You basically can. The difference is that it's quite obviously notable to a developer of any level that changing the schema in the database is something that should be done with some forethought and care. And then when that change is made, all applications accessing the database get the same new view of the schema so you're not going to have two different clients with a different idea about the schema trying to operate on the database at once because schema is global. Making a change that suuuuubtly alters the way data is stored in an unstructured object is the kind of thing that's really easy to overlook in a pull request. Your "only one module" rule is laudable, but you've also got to make sure that module can do everything you'd ever want to do to the database, including all the "one off" data mangling admin tasks anyone occasionally has to do. Otherwise it will get bypassed. Case in point I used to use an ORM heavily, but it wasn't possible to express everything in that ORM, so where that broke down we had to resort to manual sql. Add a few developers to the equation and you don't know exactly the format of your data. Good schema management practise also means you end up defining all of your schema mutating operations as migrations, leaving you with a rather vital log of how things have changed over time and a good pinch-point to catch inadvisable changes to schema.

ris 9y ago

Yup, and that's exactly the game I used to play. But when you have more than a couple of developers on a project and have ... mixed ... levels of experience, things easily get out of hand while your back is turned. Before you know it you've got a dataset with fields that are... well, you don't know what they are - they could be strings, they could be true, false, entire sub-objects or arrays - they could even be json-null. And then you have to potentially cope with all those possibilities in every place you use that field. Yes, of course you can attempt to put some sort of constraint on the data format (jsonschema perhaps?) but that's not exactly easy to perform as an in-database constraint which is the most useful and powerful form of guarantee you can have. Of course, it also leads less experienced developers away from using postgres' rich types (even datetimes are too rich for json/jsonb!) and leaving it impractical (or impossible) to perform logic on data in-query (which is often the most efficient and robust way of doing it). This road also leads to people trying to make references to other objects within json blobs. Try ensuring referential integrity on that! It all comes down to: you have a schema, whether you think you do or not, and if you let the database know about that schema it can help you manage it.