Schema-on-Write Versus Flexible Attributes

Learning outcomes

Schema-on-write validates structure before or during persistence. Flexible attribute designs delay some structural decisions and allow records of the same broad type to carry different attributes. Neither approach is universally superior. The right choice depends on stability, governance, queryability, and the cost of invalid data.

Schema-on-write

Part(  part_id BIGINT PRIMARY KEY,  sku TEXT NOT NULL UNIQUE,  description TEXT NOT NULL,  manufacturer_id BIGINT NOT NULL REFERENCES Manufacturer)

Data must satisfy the declared structure before it becomes persistent source-of-truth state.

Benefits of strict schemas

• clear domains;
• predictable queries;
• declarative constraints;
• discoverable metadata;
• stable application contracts;
• better optimizer statistics.

Cost of strict schemas

Adding new fields may require:

• migration;
• application changes;
• backfill/default strategy;
• compatibility planning.

Flexible attributes

One pattern:

Part(  part_id,  sku,  description,  attributes_json)

Different Part families can store different optional properties.

Flexibility moves responsibility

When the table no longer declares every field, someone else must define:

• allowed names;
• types;
• required/optional status;
• units;
• validation;
• display rules.

Metadata-driven attribute definitions

AttributeDefinition(  attribute_code,  display_name,  data_type,  unit_code,  required_flag)

This can make flexible attributes discoverable and governed.

Per-category attributes

CategoryAttribute(  category_id,  attribute_code,  required_flag,  sort_order)

Now the system can say pumps require max_pressure while cables require conductor_count.

Where values live

Options include:

• JSON document;
• EAV rows;
• subtype tables;
• wide sparse table;
• separate document store.

Lesson 4 examines EAV in depth.

Discoverability

A database analyst can inspect normal columns easily. Flexible metadata requires documentation and tooling so users know which attributes exist and how to query them.

Query stability

Strict column:

WHERE max_pressure_bar >= 20

Flexible JSON:

WHERE (attributes_json->>'max_pressure_bar')::numeric >= 20

The latter needs stable path/type assumptions and often specialized indexing.

Unit consistency

Flexible attributes can become dangerous if one record stores:

"pressure": 20   -- bar?

and another:

"pressure": 290  -- psi?

Attribute metadata must define units or values must carry them explicitly.

Promote maturing attributes

An attribute that starts experimental may later become:

• required for most rows;
• frequently filtered;
• integrity-critical;
• used in joins.

At that point, promote it into a normal column or related entity.

Schema evolution still exists

Flexible storage avoids some DDL migrations but introduces semantic migrations:

"temperature" -> "temperature_c"string -> numericlegacy code -> standardized code

Old documents must remain interpretable.

Validation versions

schema_version = 5

lets new validators coexist with historical records.

WorkshopHub strategy

Practice: strict or flexible?

Summary and next lesson

Schema-on-write gives strong contracts and queryability; flexible attributes reduce friction when shapes vary. Flexibility shifts rather than removes schema responsibility. The next lesson examines polymorphic associations, another common attempt to make schemas more generic.

References

• Martin Kleppmann, Designing Data-Intensive Applications.
• Bill Karwin, SQL Antipatterns.
• DBMS documentation for JSON and generated columns.