MongoDB queries don’t always return all matching documents

You have a house with an array of rooms, and each room has a house with an array of rooms? Etc.

Unless that's a reference to a house in the room class, that is not in any way a good design. And if it is a reference, then you implement it like this:

House is a table

Room is a table that has a foreign key that references the House primary key.

Can't get much simpler than that!

What do you mean what do I mean? I just gave you an example. You don't seem to understand that "consistency" in a database, is simply defined within the realms of the database itself. It isn't a universal concept of "consistency". It refers to the fact that the database will enforce certain constraints and checks. You can even denormalise your data and make it so that a persons name is different in different places. My whole point, which none of you are able to grasp because you aren't programmers, is that there are MORE CONSTRAINTS then there are options available for constraints in SQL. There are business constraints that lead to "consistency" in the real-world usage of the term, issues.

Consistency in database terms is limited by the types checks the database performs. Someone earlier said you can write any types of checks you want because pgsql supports writing constraints in any programming language you want. This does not counteract my point which was to refute the original comments proposition that IN MONGODB YOU HAVE TO WRITE YOUR CONSTRAINTS IN THE CODE AND THAT IS NOT THE CASE WITH SQL.

Jesus guys, you are slow learners.

Here's one:

You're dealing with a torrent of incoming semi-unstructured data, where losing a good chunk of it is minor nuisance because you only need a decent sample, from which you extract data.

In those kind of scenarios, making it easy to work on the code can often be far more important than reliability.

I have a project like that now. I'd love to use Postgres, and probably will eventually once things "settle down" and we know what data we need to store. But or now MongoDB is the "quick and dirty" solution. We define a schema client side for everything we nail down, so as we nail down more aspects of what data to process, it gets easier to transition to a proper database.

As ORMs get better support for Postgres' JSON capabilities, it will likely get less and less appealing to use MongoDB for stuff like this too.

ORMs are a really bad attempt to force a square peg into a round hole. The mismatch between the relational model and object-oriented design principles is simply too big.

In the relational model:

(0) A relation is a collection of tuples of primitive values. Every relation has a relation schema, which determines the arity of its tuples and the type of each tuple component. In other words, the relational model is first-order.

(1) There are a few basic operators for computing relations from other relations (relational algebra).

(2) There is a mathematical theory (database normalization) of how to design primitive relation schemas to avoid storing duplicate information, and running into insertion, update and deletion anomalies.

On the other hand, in a pure object-oriented program:

(0) An object is a collection of data and operations on it. The data is hidden from the rest of the program, so the only way to operate on it is to use the object's operations. The operations may take objects as arguments and return objects as results, so objects are intrinsically higher-order.

(1) In general, there are no limits on how one can define a single object's operations. However, it's impossible to define operations which require knowledge of the internal representation of two or more objects at a time.

(2) There are heuristic guidelines (e.g., SOLID principles) for designing flexible object-oriented systems. However, they lack any sort of rigorous foundation beyond “it seems to work in practice”, so object-oriented designers may deviate from these guidelines at their own discretion.

---

For data-oriented applications, it's pretty clear to me that the relational model has important advantages over object-orientation:

(0) The decoupling between data and operations allows the database designer to focus exclusively on data integrity constraints, instead of anticipating whatever queries users will want to make.

(1) The limited expressiveness of relational algebra (with no recursively defined relations) is also a blessing, because it makes automated query optimization tractable in practice.

While objects present problem after problem:

(0) Object graphs are intrinsically directed, and must be traversed in the direction of its links. This makes queries less declarative.

(1) Objects have a notion of identity, which destroys many opportunities for using equational reasoning to build large queries. This also makes queries less declarative.

Of course, the relational model says nothing about general-purpose programming, whereas object-orientation does. But there exist other paradigms for general-purpose programming that are less badly in conflict with the relational model. For instance, functional and logic programming:

(0) Don't reject the use of first-order data, decoupled from operations.

(1) Prefer the notion of mathematical variable, whose meaning is given by substitution (a first-order operation), to imperative assignment, whose meaning is given by certain predicate transformers (intrinsically higher-order gadgets).

Your code enforces consistency and detects when a transaction fails the consistency rule rolls back the changes to the state when the transaction started? That's some great code I'm sure, but a lot of effort.

The only limitation on an SQL check constraint is that you cannot use columns in other tables.

As has been pointed about by many of us slow learners, it's far easier to understand constraints in SQL than it often is to dive into a code base.