Relational or Document

We started with a products table and found that a column for every merchant-defined attribute means a schema change each time a merchant needs a new attribute. Then we built a document design for Shopend. But a column for each attribute is not the only relational design. Could we meet the same requirements and keep the relational database?

An attribute table

Instead of a column for each attribute, we can store each attribute as a row in a separate table:

product_attributes
  product_id         integer   foreign key -> products, not null
  name               text      not null
  value              text      not null
  primary key (product_id, name)

Here are the attributes of the same three products we used in the design with a column for each attribute:

Now a merchant can add an attribute without a schema change, because a new attribute is just one more row in this table. The primary key makes sure each product has a given attribute name at most once. This design is called entity-attribute-value, or EAV. The product is the entity, and each row holds one attribute and its value.

To read a product with its attributes, we combine its row in products with the matching rows in product_attributes. We already know how to combine related tables. The new part is that we must assemble the result into the product and its list of attributes for the API.

Filtering by several attributes is more involved. Suppose a shopper wants blue shirts in medium. The color and size are in different rows, so the query must find products that have both a color row with value blue and a size row with value medium. We can write this with joins, subqueries, or grouping. But each attribute condition we add means another join, subquery, or grouping condition in the query.

In this design, every value is text. As stored, a value such as 40 hours cannot be compared as a number. The same is true of the text values in our document examples. EAV does not require every value to be text, but supporting numbers and other types would take a richer design. Whichever storage model we use, we must decide whether the application only displays the attributes or also interprets their values.

A JSON column

Many relational databases can store JSON in a column. For example, PostgreSQL provides the jsonb type. We can add one column to the products table:

products
  ...
  attributes         jsonb     not null

The blue linen shirt’s row holds this value in its attributes column:

{ "size": "medium", "color": "blue", "fabric": "linen" }

Each product now keeps its attributes in its own row. The attribute names are whatever its merchant chose. Adding an attribute does not need a schema change. PostgreSQL can query and index values inside the JSON, so a query can test for both color and size within this object.

The other columns keep their types and constraints. Only the attributes go into JSON, because they are the part of a product that varies from merchant to merchant. Carts, orders, and their items can stay in relational tables, with foreign keys and the transactions we already know how to design.

Choosing for Shopend

All three designs let a merchant add an attribute without asking us to add a column. They differ in how they organize the data:

• EAV. A product’s attributes are separate rows in an attribute table. We assemble them with the product, and we combine attribute conditions across rows.
• Relational with JSON. A product’s attributes are an object in its row. We keep the surrounding relational design and use JSON operations for the attributes.
• Document database. A product’s attributes are an object in its document. We design document boundaries, embedded data, and references for the application.

If the shop API is already built on a relational database, a JSON column lets us add flexible attributes and keep the other tables and columns as they are. Moving to a document database would mean changing the data access code. It would also mean deciding how to store carts, items, and orders, as we did in this chapter.

In the document design, cart items are inside the cart document, and order items are inside the order document. That works well when we read a cart or an order with its items. Products are shared, so they stay in their own documents, and we still have to read them to get the cart’s current product details. Checkout still needs a transaction across the order, product, and cart documents.

For Shopend, we could use a relational database with a JSON column for product attributes, or we could use the document design we developed. The need for flexible attributes does not settle the choice by itself. We also have to consider the rest of the data, how the application reads and changes it, the guarantees those changes require, and the database we already run.