Types, Constraints & Database Objects
JSON, Arrays, and Composite Data
Nested types are useful when a value is naturally retrieved and changed as one bounded aggregate, but relational decomposition is superior when elements need independent identity, constraints, joins, lifecycle, or selective updates.
- JSON preserves a document shape, not a relational contract by itself.
- Arrays model ordered or repeated values inside one row.
- Composite values group named fields.
- Relational decomposition exposes keys and dependencies.
- Duplication can be intentional for immutable snapshots.
- Query workload cannot override integrity requirements silently.
| Need | Prefer | Why |
|---|---|---|
| Independent element identity or FK | Child relation | Keys and referential integrity |
| Many joins/filters on elements | Child relation | Direct statistics and indexes |
| Bounded immutable snapshot | JSON/composite | Read and version as one value |
| Ordered primitive list with no element metadata | Array, if portable enough | Order is intrinsic and ownership is singular |
| Unbounded or concurrently edited collection | Child relation | Avoid rewriting/locking one large value |
CREATE TABLE order_delivery_snapshots (
order_id BIGINT PRIMARY KEY REFERENCES orders(order_id),
address_document JSON NOT NULL,
captured_at TIMESTAMP WITH TIME ZONE NOT NULL
);
-- Keep product-bearing lines relational: order_items already provides
-- order_id, product_id, quantity, unit_price and their constraints.
SELECT i.order_id, i.product_id, i.quantity
FROM order_items AS i
WHERE i.product_id = :product_id;The added snapshot is explicitly defined before use and represents the address as purchased, not the customer’s current address. A JSON type can verify syntactic JSON; stronger shape validation is a separate contract. Product-bearing line items remain decomposed because products have identity and line-level constraints.