Dev Learning Lab

The labs on this site need JavaScript to run. The same content is written out below.

Learn › Database › Scaling and distribution

NoSQL and the document model

Split an order into three tables, or put it in one document?

Store the same data (orders and order items) in two ways. The relational way splits products, orders, and order items into three tables and joins them when reading. The document model is the way used by document databases such as MongoDB: it treats one order as one document and puts the array of items and even the product names inside it.

Partly checked against real execution · The document model was reproduced with a MySQL 8.4.11 JSON column, and 20 combinations were checked by execution. A dedicated document database was not run

Concepts in this lab

NoSQL
A general name for databases that do not use the relational model. They include key-value stores (Redis), document databases (MongoDB), wide-column stores (Cassandra), and graph databases. Many are designed to store data flexibly without a fixed schema and to scale out easily across several servers, but joins and complex transactions are more limited than in relational databases.
Document model (document database)
A way of storing data in units called documents, instead of rows and columns. A document usually has a JSON-like shape, and a value in it can hold an array or another object. So data that is read together, such as an order and its items, can be kept in one document and read in one go without a join. MongoDB is the best-known document database. If you copy the same value into several documents, you must fix every copy when the value changes, so deciding what goes inside a document and what is kept separately is the heart of the design.
Fixed schema and flexible schema
A schema is the structure that decides what items the data is made of and what data type each item has. A relational database decides the columns first when a table is created and accepts only rows that fit that structure, and changing the structure requires running ALTER TABLE. A document database can usually store documents even when their items differ, and this is called a flexible schema. The structure is easy to change, but a wrong name or a missing field is also stored as it is, so keeping the shape right is left to the application code or to a validation feature that you turn on separately.
Normalization and denormalization
Normalization is the design process of splitting tables so that the same data is not stored redundantly in several places. With redundancy, a change may be made in only one place, and the data then becomes inconsistent. Denormalization intentionally allows redundancy or merges tables for read performance. It reduces joins, but it adds the burden of updating the redundant data together.
JOIN (INNER, OUTER)
The operation that combines rows of two tables according to a condition. INNER JOIN includes only the rows where the condition matches on both sides. LEFT OUTER JOIN includes every row of the left table and fills in NULL when there is no match on the right. Choose between them based on whether data without a match must stay in the result.

Try these

Running with the default settings

  1. The data is stored with the document model. The order_doc table has 3 documents, and one document is one order. Each document holds the person who ordered and the array of items (items) as a whole, and the product name is copied into each item.
  2. UPDATE order_doc SET doc = JSON_SET(…) WHERE …: It finds every document whose items array contains 'keyboard' and changes the name at that position in each document. The number of documents changed is 3. Because the product name is copied into every order document, you have to change as many documents as contain that product.
  3. This time all 3 were changed, so every order shows the new name. But if you stop after changing only some, the same product is left with different names in different documents. The database does not know that these values must be the same.

Result of this run

Code

-- The document model, reproduced with a MySQL JSON column. One row is one document
CREATE TABLE order_doc (id INT PRIMARY KEY, doc JSON NOT NULL);
-- Example of a document (order 1)
-- {"member": "kim",
--  "items": [{"product_id": 1, "name": "keyboard", "qty": 1},
--            {"product_id": 2, "name": "mouse", "qty": 2}]}

-- In each document, find the position of 'keyboard' in the items array and change the name at that position
UPDATE order_doc
SET doc = JSON_SET(doc, JSON_UNQUOTE(JSON_SEARCH(doc, 'one', 'keyboard', NULL, '$.items[*].name')), 'mechanical keyboard')
WHERE JSON_SEARCH(doc, 'one', 'keyboard', NULL, '$.items[*].name') IS NOT NULL;

Assumptions of this model

The document model was reproduced with a MySQL JSON column. I ran twenty combinations (two ways of storing, four things to do, and 2, 3, and 5 orders) with the same statements as on the screen on MySQL 8.4.11 on the PC used to write this lab, and checked that the following matched the screen: the error code; the number of tables and the number of result rows shown by EXPLAIN; the number of rows (documents) changed by UPDATE; the number of documents with gift; and the quantity total. I also confirmed the following facts used in the explanations by execution. Reading without a join needs three queries, one for each table. After a column is added with ALTER TABLE, the same INSERT goes in, and the existing rows also get that column. If only one document is changed, the same product is left with two different names. After a JSON_SCHEMA_VALID constraint is added, a document with a misspelled field name is rejected with error 3819. A dedicated document database (MongoDB and others) is not on this PC, so I could not run one. So what is shown here is "the shape of reads and writes when an order is stored as one document," not the exact behavior of a document database product. Query syntax, how to change a value inside an array, the schema validation feature, and support for transactions and joins across several documents differ by product and version. Which way is faster depends on the data volume, the indexes, and the product, so it is not covered, and the results on the screen are the number of places read or changed, not the speed. Other kinds of NoSQL, such as key-value stores, wide-column stores, and graph databases, are not covered.

Previous lab: Redis single-threaded execution

Concepts in this lab

What this lab shows

Settings

Code


      
    


    

      
Diagram

      

      

    


    

      

      

      

    


  


  

    

      
Run comparison

      

      

    

    

      
Try these