Re: HTTP Frontend? (and a brief thought on materialized views)

> While I was doing this I always thought this would have been a better
> approach for my previous project, an accounting application. If I could
> just have stored entities like invoice & customer as a single document that
> is inserted, updated, etc. atomically it would be a lot simpler and faster
> than having to break things out into columns and rows spread over various
> tables.

Actually, an accounting application is the *worst* candidate for
document-oriented storage. For example, it's pretty normal for
customers to have multiple invoices, and for each invoice to have
multiple line-items which relate to inventory items. Without relational
structure, answering questions like "Do I have enough green shoes in
stock to cover pending orders within the next 30 days in Texas?" goes
from a relatively simple query to a major exercise in frustration. And
it's not possible to implement required business constraints ("A
customers balance may not go below zero") at all in a document database.

There's strong arguments to be made for document-structured storage for
some applications. But RDBMS covers financial applications very well;
it's what RDBMS was designed for in the first place.

> Some fields are just "information" that isn't searched for or used
> relationally but ends up getting a field (or a whole bunch of fields, like
> your address) in the DB schema anyway. Adding, removing, and changing
> columns is always scary to me in the SQL database - when there's no schema
> you can fix these things as part of the definition of your "view" instead.

Yes, having "documents" *in addition* to relational data gives you the
best of both worlds. You can use relational structures to store data
which is well-defined and business-critical, and document structures to
store data which is undefined and not critical.

> So I kind of think the document database kind of bridges the gap between an
> OODBMS and the RDBMS because the document is like a little cluster of

OODBMS != DocumentDB

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com