The Object-Relational Mismatch
Most development takes place in object-oriented programming (OOP) languages, which leads to a common criticism of the SQL data model. If data is stored in relational tables, an awkward translation layer is required between the objects in the application code and the database model of tables, rows, and columns. This is called the object-relational impedance mismatch.
Example: LinkedIn Profile
Suppose a LinkedIn profile has a user_id (primary key). Fields like first_name and last_name appear only once per user, but fields like previous jobs and education history can contain multiple entries.
Traditionally, the most common approach was to create separate tables for jobs and education and link them with the main user table using foreign keys.
Newer approaches include using structured data types and XML or JSON documents, allowing multiple pieces of related data to exist within a single row. Many databases also support querying and indexing data inside these documents.
A third option is to encode jobs and education as a JSON or XML document, store it in a text column in the database, and let the application interpret its structure and content.
Drawback: The database cannot query values inside that encoded text column.
JSON is much better suited for representing a resume.
It also has much better locality than a multi-table schema. A single query is usually sufficient, whereas in SQL you might need multiple queries or perform messy multi-way JOIN operations.
Many-to-One and Many-to-Many Relationships
Regions, for example, are stored as IDs instead of plain text strings.
It might seem reasonable to accept and store such information as plain text input. However, storing them as standardized lists of geographic regions and industries, and letting users choose from a dropdown or autocomplete field, provides several advantages:
- Consistent style and spelling
- Reduced ambiguity
- Easier updates
- Localization support
- Better search capabilities
When you store an ID such as "Philanthropy," it exists in only one place. The tuples in the database simply reference it.
Are Document Databases Repeating History?
The most popular database in the 1970s was IBM's IMS. It used a hierarchical model, which has remarkable similarities with the JSON document model.
It worked well for one-to-many relationships but was difficult to maintain for many-to-many relationships, and it did not support JOINs.
Developers had to decide whether to duplicate (denormalize) records or manually resolve references from one record to another.
The issues faced in the 1960s and 1970s are remarkably similar to those faced by modern developers using document databases today.
To solve the limitations of IMS, the relational model and the network model were introduced. The relational model succeeded, while the network model eventually faded away.
Relational vs. Document Databases
Document databases
- Better performance due to data locality
- Greater schema flexibility
Relational databases
- Native support for
JOINs - Better support for many-to-one and many-to-many relationships
Document models are sometimes called "schema-less," but that term is misleading. There is still an implicit schema.
A more accurate term is schema-on-read, where the structure of the data is implicit and only interpreted when the data is read.
Data Locality for Queries
A document is usually stored as a single continuous string, encoded as JSON, XML, or a binary format such as BSON.
If the application frequently needs to access the entire document, this storage locality provides a performance advantage.
If the same data is split across multiple tables, multiple index lookups are required to retrieve it. This may require additional disk seeks and take more time.
However, when updating documents, the entire document usually needs to be rewritten. Only modifications that do not change the encoded size can typically be performed in place.
For this reason, it is generally recommended to:
- Keep documents fairly small.
- Avoid updates that increase the size of the document.
Google's Spanner database offers similar locality properties while using a relational data model.
Convergence of Document and Relational Databases
Most relational databases (other than MySQL) have supported XML since the mid-2000s. The same is true for JSON support.
RethinkDB (a document database) supports relational-style JOINs in its query language.
Some MongoDB drivers also automatically resolve database references, effectively performing client-side JOINs.
However, client-side JOINs are generally slower than database JOINs because they require additional network round trips and cannot benefit from the database's query optimizer.
Top comments (0)