How use a relational database as a document-based one?

Question

For make a document managment system, I'm looking at document stores like MongoDB, but because I have more experience with regular databases (Firebird, Sql Server, Mysql) I wonder if is possible model a document store on top a relational one.

The advantages about a document store, schema less:

• Fit well to the task of store arbitrary metadata about files
• No need to upgrade schemas
• According to mongodb, excellent performance for BLOB like video
• Easier scalability

But with a relational one:

• Referencial integrity
• Better tooling
• More resilent to crash & corruptions
• SQL

So, how could work a relational database in this case?

Answer 1

Consider Martin Fowler's Serialized LOB pattern:

CREATE TABLE Documents (
  documentid SERIAL PRIMARY KEY,
  -- fixed relational attributes ...
  document TEXT -- contains XML, YAML, whatever
);

You can put any semi-structured data with dynamic attributes into the document column. You just can't easily use SQL predicates to search or sort by fields in that blob. But you couldn't anyway -- variable attributes is a non-relational concept, and it's awkward to support them in SQL no matter what.

You can use a hybrid approach, storing some fixed attributes in conventional columns, and all the variable attribute stuff in the blob.

This points out why document-oriented databases exist. They are designed to solve a problem that the relational paradigm has chosen not to support. But document-oriented databases don't do some of the cool things that relational databases do, like referential integrity and even data type coherency.

Answer 2

A simple MySQL example:

CREATE TABLE Docs (
  id INT,
  attr VARCHAR(255),
  value BLOB,
  PRIMARY KEY (id, attr),
  KEY attr_index (attr)
)

Once you have that you can add any attribute to a document and stuff anything in the value, and you can use self joins on the document table to do complex queries like:

SELECT * FROM Docs AS d1, docs AS d2 WHERE d1.attr = "foo" AND d2.attr = "bar"

Which returns documents with both foo and bar attributes.