Create an Initial Duality View

Create the initial duality view that exposes the baseline canonical storage model used throughout the schema evolution patterns in this chapter. Every remaining topic evolves this model while preserving compatibility for applications that continue using earlier document shapes.

Dave starts with a small lemonade stand. Although the business has only one cart today, he expects to expand to multiple locations. Stand information is therefore modeled separately from sales so that each stand has its own identity, location, and pricing information.

A sale records who bought lemonade, how many cups were sold, how much was charged, and which stand fulfilled the order.

This initial model intentionally makes several simplifying business assumptions. A sale represents one product. Customer information belongs directly to the sale. Stand location is represented by one field. These assumptions are appropriate for a small business, but they will gradually change as the business grows.

This topic introduces the first JSON-relational duality view. Unlike the compatibility APIs introduced later in the chapter, this view is not intended to remain a long-term contract. Its purpose is to expose the initial canonical storage model from which every remaining evolution pattern begins.

Relational Schema

The initial relational model intentionally contains only two business entities. Keeping the starting model simple makes the later evolution patterns easier to understand because each subsequent topic evolves one of these original design decisions.

Table	Role
sales	Stores sale-level information such as date, quantity, amount, customer name, email address, and stand reference.
stands	Stores stand-level information such as location and price per cup.

Initial Relational DDL

CREATE TABLE stands (
  stand_id       INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
  location       VARCHAR2(100) NOT NULL,
  price_per_cup  NUMBER(10, 2) DEFAULT 2.5 NOT NULL,
  CONSTRAINT stands_pk PRIMARY KEY (stand_id)
);

CREATE TABLE sales (
  sale_id        INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
  sale_date      DATE DEFAULT SYSDATE,
  quantity       NUMBER NOT NULL,
  amount         NUMBER(10, 2) NOT NULL,
  customer_name  VARCHAR2(50) DEFAULT 'User' NOT NULL,
  email_address  VARCHAR2(100) DEFAULT NULL,
  stand_id       INTEGER DEFAULT 1 NOT NULL,
  CONSTRAINT sales_pk PRIMARY KEY (sale_id),
  CONSTRAINT sales_stand_fk FOREIGN KEY (stand_id) REFERENCES stands (stand_id)
);

The relationship is:

sales.stand_id -> stands.stand_id

Each sale references exactly one stand. Although the information is stored in two relational tables, applications work with a single JSON document.

At this point, the canonical storage model is simple. A sale owns customer information directly, references one stand, and implicitly represents one product. Later topics evolve each of those assumptions as the business grows.

First Duality View

sales_view exposes the canonical storage model as a single JSON document. Applications work with one business object, while the underlying storage model remains relational.

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW sales_view AS
sales @insert @update @delete
{
    _id: sale_id,
    date: sale_date,
    quantity,
    amount,
    customer_name,
    email_address,
    stand_info: stands @insert @update @nodelete
    {
        _id: stand_id,
        location,
        price_per_cup
    }
};

The application sees a document shaped like this:

{
    "_id": 1,
    "date": "2026-01-12",
    "quantity": 8,
    "amount": 22.00,
    "customer_name": "nora",
    "email_address": "[e-mail]",
    "stand_info": {
        "_id": 1,
        "location": "MARKET CART, NORTH PLAZA",
        "price_per_cup": 2.75
    }
}

What the View Introduces

Although this example is intentionally simple, it establishes the architectural separation that every remaining topic builds upon.

Applications work with a JSON sale document. The database stores the same business information in relational tables. The duality view connects those two representations without requiring applications to understand the underlying storage model.

Design decision	Why it matters
Sale is the document root.	A sale is the business transaction applications naturally read and write.
Stand is represented as a nested object.	Stand information belongs with each sale document while remaining an independent business entity in the canonical storage model.
@nodelete	The lifetime of a stand is independent of any individual sale.

This separation between the document API and the canonical storage model is the architectural foundation for every evolution pattern that follows. Later topics evolve the JSON shape, the relational schema, or both, while preserving compatibility for existing applications.

Evolution Check

At this point, the canonical storage model reflects a deliberately simple business model:

• A sale references one stand.
• Customer information belongs directly to each sale.
• Product information belongs directly to the sale.
• Stand location is represented by one field.
• Each sale represents one product.

The remaining topics evolve one assumption at a time. As the business grows, the canonical storage model changes to reflect new business requirements, while compatibility APIs preserve earlier document shapes for existing applications.

sales_view is only the introductory view. The first durable compatibility API begins in the next scenario with sales_v1.