Move Embedded Data to Shared Data

Move repeated customer fields into a shared customer entity while preserving the compatibility API.

Extracting repeated embedded data into a first-class shared entity.

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, ownership rules, and lifecycle.

Best Practice

Move embedded data into a shared entity when the repeated values develop their own identity, ownership, or lifecycle.

Shared business entities should usually be stored once and referenced from multiple parent rows. Compatibility APIs can continue exposing the earlier embedded shape while applications migrate.

Evolution Goal

This scenario demonstrates entity extraction: customer data moves out of sales and becomes a first-class entity in customers.

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.

Best Practice: Normalize embedded data into shared referenced data when the repeated values need their own identity and lifecycle. When the data is shared, make the ownership and delete rules explicit.

Design Decision

Customer becomes a first-class business entity instead of copied sale data.

The canonical storage model changes from each sale carrying customer text to many sales referencing one customer row. The compatibility API continues to expose the embedded fields. The evolved API exposes the customer entity directly.

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 and duplicated into each sales transaction for the same customer. 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 who owns customer identity: the database or the application.

Case 1: Normalize customer data with a uniqueness rule

In the first approach, the database owns customer identity.

A customer is identified by the pair customer_name and email_address. If multiple sales carry the same values, they reference the same single customer row.

Recommendation

Best when

The business rule says a name and email pair should represent one shared customer.
Duplicate customer identities should be prevented in the database.
The database should own the identity rule.

Avoid when

The same name and email can legitimately represent different people.
Identity rules change frequently.
The application needs to decide whether rows are shared or separate.

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 canonical storage model now keeps customer data in customers, and the uniqueness constraint prevents duplicate 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 sale attributes. 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 preserves the old root-level customer shape.

The canonical storage 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 preserve that compatibility shape.

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 and shared consistently across applications. 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 extracts customer data into customers, but the application owns identity.

Two customer rows can have the same name and email if the business wants to treat them as separate records. The compatibility and evolved APIs keep the same shapes; only the ownership of identity changes.

Recommendation

Best when

The application should decide whether customer rows are shared or separate.
Duplicate identities may be intentional.
Identity rules are more flexible than a database uniqueness constraint.

Avoid when

The database must enforce a single shared customer identity.
Multiple applications would need to keep identity rules synchronized.
Delete behavior cannot be controlled consistently.

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, rather than a 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 defined by (customer_name, email_address). 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.

Design Comparison

Strategy	Identity rule	Flexibility	Long-term recommendation
Case 1	Database-enforced uniqueness	Moderate	Preferred when one customer identity should exist once
Case 2	Application-managed identity	High	Useful when duplicates may be intentional

Case 1 provides stronger database-enforced identity. Case 2 is more flexible, but it requires stronger application discipline and clearer ownership rules. Case 2 is an intermediate stage between embedded customer information in the sales table and fully shared customer identities as described in Case 1.

Evolution Thought Process

The key architectural decision is that customer becomes a first-class business entity instead of copied sale data.

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

sales_v1 hides that storage change from older clients by using @unnest to preserve the root-level fields.

sales_v2 exposes the evolved model directly by presenting customer_details as a referenced object.

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.

Design Trade-offs

Question	Case 1	Case 2
Who owns identity?	Database	Application
Duplicate customer rows allowed?	No	Yes
Normalization strength	Strong	Flexible
Best fit	Stable business identity rules	Application-driven identity rules

Evolution Check

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

customer_name and email_address no longer need to be stored directly on sales.
sales references customers through customer_id.
sales_v1 continues to expose customer_name and email_address at the root.
sales_v2 exposes customer details as a nested referenced object.
Customer normalization can be done with or without a uniqueness rule.
Delete behavior must match whether customer rows are shared data or owned data.

Topic Takeaway

This scenario demonstrates entity extraction: repeated embedded data becomes a first-class shared entity with its own identity and lifecycle.

The canonical storage model now stores customer data once and references it from many sales. The compatibility API continues to expose the embedded fields so existing applications keep working.

The important design principle is to make ownership explicit. When data becomes shared, either the database or the application must own identity, and delete behavior must match that ownership model.

The cost is that the compatibility API can no longer create customer identity by accident. Sending the same embedded customer values now resolves to an existing shared row rather than a private copy, so an edit made through the old shape reaches every sale that references that customer.