Attributes as Columns

Assume we stored the shop API’s products in a relational database. Here is a products table we could use. It is the shop API’s table with one change for Shopend: each product records the shop it belongs to.

products
  product_id         integer   primary key
  shop_id            text      not null
  name               text      not null
  description        text      not null
  price_cents        integer   not null, check price_cents >= 0
  quantity           integer   not null, check quantity >= 0
  deleted_at         timestamp null

We decide every column of this table when we design it, and we give each column a type. A mug’s glaze and a shirt’s size are not among those columns, so there is nowhere to store them.

We could add a column for each attribute. Here are three products from three shops, with the price, stock, and description columns left out:

Each product has a value only in the few columns that apply to it. The rest of its columns are null.

A bigger problem is that the merchants choose the attributes. Suppose a new merchant sells bicycles and needs a frame size. Then we have to add a frame_size column to the products table. That table holds the products of every shop. Changing the schema of a table is database work that we plan and run ourselves, so a merchant cannot add an attribute on their own. With many shops, the table would soon have hundreds of columns. Most of them would be null in almost every row.

Two merchants can also use the same attribute name for different kinds of values. For threadline, size is text: small, medium, or large. A candle shop might use size for a numeric height in centimeters. A single size column has one type, so we have to choose how to represent both. We could store everything as text, but then the database cannot check that the candle’s height is a number.

A relational table has one set of columns, and every row has all of them. That works for the details every product has, such as its name, price, and stock. It does not work for attributes, because each merchant decides the attributes of their own products, and those attributes differ from shop to shop.