Create an Initial Duality View

Create the initial sales duality view that the schema evolution examples use as a starting point.

Dave starts a small lemonade operation.

It begins with one cart, but Dave is already planning for more locations. He keeps stand information separate from the sale because a stand has its own identity, location, and price per cup. That makes the first model simple enough to understand, but still ready for the business to grow.

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

This topic introduces the first JSON-relational duality view. It is not the long-term compatibility point for later evolution. It simply shows the first document shape before the business starts changing.

Relational Schema

The baseline schema starts with two tables.

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

A sale belongs to a stand. The application can still work with one JSON document.

First Duality View

sales_view exposes the sale as a JSON document and nests the stand details inside stand_info.

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

The application sees a JSON sale document. The database stores the sale and stand details in relational tables. The duality view connects the document shape to the relational shape.

Duality view feature	Meaning here
sales @insert @update @delete	The root object is backed by the sales table and supports document-level inserts, updates, and deletes.
stand_info: stands	Stand data appears as a nested object inside the sale document.
@nodelete	Keeps a stand from being deleted when a sale document is deleted.

This is the first idea needed for the rest of the evolution. The application API can be JSON while the storage model remains relational.

Evolution Check

This scenario confirms the basic document shape.

• A sale document exposes fields from sales.
• The same document includes nested fields from stands.
• Customer details are still embedded directly in sales.
• Stand location is still one field.
• Stand pricing is still part of stand_info.

sales_view is only the introductory view. The durable compatibility API begins in the next scenario, where product fields are added and the evolved sale shape becomes sales_v1.