Move Embedded Data to Shared Data

Move repeated customer fields into shared data while preserving older sales documents.

Moving embedded fields into referenced shared data.

Business Step

Dave’s sales records still carry customer information directly on each sale.

That was fine when the business was small. But as the business grows, the same customer can appear in many sales. Storing customer_name and email_address on every sale starts to repeat the same information across the system.

Dave now needs customer data to behave like shared business data.

A sale should still belong to a customer, but the customer should have its own identity.

Evolution Goal

This scenario moves customer data out of the sales table and into a separate customers table.

sales_v1 remains the older compatibility API. It continues to expose customer_name and email_address at the root of the sale document.

sales_v2 is the newer API. It exposes customer data as a nested customer_details object.

Documentation view Role
sales_v1 Older compatibility API. Existing clients continue to read and write customer_name and email_address at the root.
sales_v2 Newer API. Newer clients read and write customer_details as referenced customer data.

Shape Change

The older customer shape is embedded directly in the sale document.

{
    "_id": 16,
    "customer_name": "lena",
    "email_address": "lena@example.com"
}

The newer customer shape moves those fields into customer_details.

{
    "_id": 16,
    "customer_details": {
        "_id": 7,
        "name": "lena",
        "email": "lena@example.com"
    }
}

The customer is no longer just copied text on the sale. It becomes a referenced object with its own identity.

Cases in This Topic

There are two ways to handle the shared customer model.

Case Approach
Case 1 Create customers with a uniqueness rule for customer_name and email_address.
Case 2 Create customers without a uniqueness rule, allowing the application to control whether customer rows are shared or separate.

Both cases preserve the older JSON shape. The difference is how strongly the relational schema enforces customer identity.

Case 1: Normalize customer data with a uniqueness rule

In the first approach, Dave wants each customer identity to be unique.

A customer is identified by the pair customer_name and email_address. If multiple sales have the same customer information, they point to the same customer row.

Case 1 Preprocessing

The preprocessing creates a customers table, seeds it from the existing sale data, adds a customer_id reference to sales, and then removes the embedded customer fields from sales.

CREATE TABLE customers (
  customer_id    INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
  customer_name  VARCHAR2(50) NOT NULL,
  email_address  VARCHAR2(100),
  CONSTRAINT customers_pk PRIMARY KEY (customer_id),
  CONSTRAINT customers_uk UNIQUE (customer_name, email_address)
);

INSERT INTO customers (customer_name, email_address)
SELECT customer_name, email_address
FROM sales
WHERE customer_name IS NOT NULL
GROUP BY customer_name, email_address
ORDER BY MIN(sale_id);

ALTER TABLE sales ADD (customer_id INTEGER);

UPDATE sales s
SET customer_id = (
  SELECT c.customer_id
  FROM customers c
  WHERE c.customer_name = s.customer_name
    AND c.email_address = s.email_address
);

ALTER TABLE sales MODIFY (customer_id NOT NULL);
ALTER TABLE sales ADD CONSTRAINT sales_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers (customer_id);

ALTER TABLE sales DROP COLUMN customer_name;
ALTER TABLE sales DROP COLUMN email_address;

After this preprocessing, sales no longer stores customer_name or email_address directly. It stores customer_id.

The customer fields now live in customers, and the uniqueness constraint prevents duplicate customer identities for the same name and email pair.

The relational shape after moving customer information out of the sales table is shown below.

SQL> DESCRIBE sales;

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
SALE_ID                                   NOT NULL NUMBER(38)
SALE_DATE                                          DATE
QUANTITY                                  NOT NULL NUMBER
AMOUNT                                    NOT NULL NUMBER(10,2)
STAND_ID                                  NOT NULL NUMBER(38)
PRODUCT_TYPE                              NOT NULL VARCHAR2(40)
TAGS                                      NOT NULL VARCHAR2(40)
CUSTOMER_ID                               NOT NULL NUMBER(38)

SQL> DESCRIBE customers;

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
CUSTOMER_ID                               NOT NULL NUMBER(38)
CUSTOMER_NAME                             NOT NULL VARCHAR2(50)
EMAIL_ADDRESS                                      VARCHAR2(100)

The important change is that customer_name and email_address are no longer columns on sales. The sales table now stores customer_id, which references the customer row that owns those details.

The relationship is:

sales.customer_id -> customers.customer_id

sales_v1 Compatibility View

sales_v1 keeps the old root-level customer shape alive.

The relational model now uses a customers table, but older clients still see customer_name and email_address directly on the sale document. The view uses an unnested customers mapping to make referenced customer fields appear at the root.

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW sales_v1 AS
sales @insert @update @delete
{
  _id: sale_id,
  date: sale_date,
  quantity,
  amount,
  product_type,
  tags,
  customers @insert @update @nodelete @unnest
  {
    customer_id: customer_id @hidden,
    customer_name: customer_name,
    email_address: email_address
  },
  stand_info: stands @insert @update @nodelete
  {
    _id: stand_id,
    location,
    stand_pin: pin @hidden @generated(path: "$.stand_info.location.substr(0, 6)", on: "write"),
    stand_address: address @hidden @generated(path: "$.stand_info.location.substr(7)", on: "write"),
    price_per_cup @generated(sql: "2.75")
  }
};

The important feature here is @unnest.

customers is now a referenced table, but @unnest keeps customer_name and email_address at the root of the JSON document. That preserves the older API without putting those columns back into sales.

customer_id is marked @hidden because sales_v1 needs it to maintain the relationship between sales and customers, but older clients should not see it as part of the document API.

In other words:

Field Why it appears this way
customer_name Visible because older clients already depend on it.
email_address Visible because older clients already depend on it.
customer_id Hidden because it is a relational helper for the new customer reference, not an old-API field.

sales_v2 Evolved View

sales_v2 exposes the referenced customer as a nested object.

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW sales_v2 AS
sales @insert @update @delete
{
  _id: sale_id,
  date: sale_date,
  quantity,
  amount,
  product_type,
  tags,
  customer_details: customers @insert @update @nodelete
  {
    _id: customer_id,
    name: customer_name,
    email: email_address
  },
  stand_info: stands @insert @update @nodelete
  {
    _id: stand_id,
    stand_pin: pin,
    stand_address: address
  }
};

The newer document shape looks like this:

{
    "_id": 18,
    "date": "2026-01-28",
    "quantity": 10,
    "amount": 45.00,
    "product_type": "Pear Lemonade",
    "tags": "seasonal",
    "customer_details": {
        "_id": 8,
        "name": "soren",
        "email": "soren@example.com"
    },
    "stand_info": {
        "_id": 10,
        "stand_pin": "560010",
        "stand_address": "gallery cart, west hall"
    }
}

Case 1 Behavior

API or schema object Behavior
sales_v1.customer_name Appears at the root for older clients, even though the value comes from customers.
sales_v1.email_address Appears at the root for older clients, even though the value comes from customers.
sales_v2.customer_details Exposes the referenced customer as a nested object.
sales.customer_id Stores the reference from a sale to a customer.
customers Stores customer identity and contact information.
customers_uk Enforces one customer row per customer_name and email_address pair.

When to use this strategy

Use this approach when customer identity should be enforced by the database. It is a good fit when the business rule says that the same name and email pair should represent one shared customer.

Case 1 Limitation

The uniqueness rule is only as good as the identity rule behind it. If two different people can share the same name and email value, the constraint may merge identities that should stay separate.

sales_v2 uses @nodelete for customer_details. Deleting a sale document should not delete the shared customer row, because that customer may be referenced by other sales.

Case 2: Normalize customer data without a uniqueness rule

The second approach still moves customer data into customers, but it does not enforce uniqueness on customer_name and email_address.

This gives the application more control. Two customer rows can have the same name and email if the business wants to treat them as separate customer records.

The old and new JSON APIs still work the same way. The difference is the relational rule behind the customer table.

Case 2 Preprocessing

The preprocessing creates the same shared customer structure, but without the uniqueness constraint.

CREATE TABLE customers (
  customer_id    INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
  customer_name  VARCHAR2(50) NOT NULL,
  email_address  VARCHAR2(100),
  CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

INSERT INTO customers (customer_id, customer_name, email_address)
SELECT ROW_NUMBER() OVER (ORDER BY MIN(sale_id)) AS customer_id,
       customer_name, email_address
FROM sales
WHERE customer_name IS NOT NULL
GROUP BY customer_name, email_address;

ALTER TABLE sales ADD (customer_id INTEGER);

UPDATE sales s
SET customer_id = (
  SELECT c.customer_id
  FROM customers c
  WHERE c.customer_name = s.customer_name
    AND c.email_address = s.email_address
);

ALTER TABLE sales MODIFY (customer_id NOT NULL);
ALTER TABLE sales ADD CONSTRAINT sales_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers (customer_id);

ALTER TABLE sales DROP COLUMN customer_name;
ALTER TABLE sales DROP COLUMN email_address;

After this preprocessing, sales still references customers through customer_id. The difference is that customers does not prevent repeated name and email values.

That makes the model more flexible, but it moves responsibility for customer identity out of the uniqueness constraint.

sales_v1 Compatibility View

sales_v1 uses the same compatibility shape as Case 1.

Older clients still see customer_name and email_address at the root. The values come from customers through an unnested mapping.

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW sales_v1 AS
sales @insert @update @delete
{
  _id: sale_id,
  date: sale_date,
  quantity,
  amount,
  product_type,
  tags,
  customers @insert @update @nodelete @unnest
  {
    customer_id: customer_id @hidden,
    customer_name: customer_name,
    email_address: email_address
  },
  stand_info: stands @insert @update @nodelete
  {
    _id: stand_id,
    location,
    stand_pin: pin @hidden @generated(path: "$.stand_info.location.substr(0, 6)", on: "write"),
    stand_address: address @hidden @generated(path: "$.stand_info.location.substr(7)", on: "write"),
    price_per_cup @generated(sql: "2.75")
  }
};

sales_v2 Evolved View

sales_v2 exposes customer_details as a nested referenced object.

In this case, customer_details allows delete through the nested customer object.

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW sales_v2 AS
sales @insert @update @delete
{
  _id: sale_id,
  date: sale_date,
  quantity,
  amount,
  product_type,
  tags,
  customer_details: customers @insert @update @delete
  {
    _id: customer_id,
    name: customer_name,
    email: email_address
  },
  stand_info: stands @insert @update @nodelete
  {
    _id: stand_id,
    stand_pin: pin,
    stand_address: address
  }
};

The newer document shape is the same customer_details shape, but the relational identity rule is different.

{
    "_id": 20,
    "date": "2026-01-30",
    "quantity": 7,
    "amount": 31.50,
    "product_type": "Plum Lemonade",
    "tags": "seasonal",
    "customer_details": {
        "_id": 9,
        "name": "tomas",
        "email": "tomas@example.com"
    },
    "stand_info": {
        "_id": 12,
        "stand_pin": "560012",
        "stand_address": "arcade cart, main hall"
    }
}

Case 2 Behavior

API or schema object Behavior
sales_v1.customer_name Remains a root-level field for older clients.
sales_v1.email_address Remains a root-level field for older clients.
sales_v2.customer_details Exposes customer data as a nested referenced object.
sales.customer_id Stores the reference from a sale to a customer.
customers Stores customer rows without enforcing uniqueness by name and email.
customer_details @delete Allows the nested customer row to participate in delete behavior through the newer API.

When to use this strategy

Use this approach when the application, not the database constraint, should decide whether customer rows are shared or separate. It is more flexible than Case 1, but it requires stronger application discipline.

Case 2 Limitation

Without a uniqueness constraint, the database does not prevent duplicate customer identities. Two rows can carry the same customer_name and email_address.

Allowing @delete on customer_details is safe only when the delete behavior matches the ownership model. If a customer row is shared by multiple sales, deleting it through one sale can be dangerous.

Evolution Thought Process

This topic changes where customer data lives.

Before this scenario, customer_name and email_address were sale fields. After this scenario, customer data belongs to customers, and sales stores customer_id.

sales_v1 hides that storage change from older clients. It uses @unnest so customer_name and email_address still appear at the root of the sale document.

sales_v2 exposes the new model more honestly. It shows customer_details as a nested object because the customer is now referenced shared data.

Feature Why it matters
@unnest Lets referenced customer fields appear at the root of sales_v1.
@hidden Keeps customer_id out of the older JSON API while still allowing the relationship to work.
@nodelete Protects shared customer rows from being deleted through a sale-oriented API.
@delete Allows the newer API to delete the nested referenced customer row when that ownership model is desired.

Evolution Check

By the end of this scenario, the evolution proves the following:

Topic Takeaway

This scenario moves from embedded private data to referenced shared data.

Older clients still see the sale document they know. Newer clients see that customer data has its own identity.

The most important design choice is not only where the data lives. It is also who owns the referenced row. If customer data is shared, protect it. If customer data is owned by the sale API, delete behavior can be more permissive.