Announcing: Updates to the Customer Orders Sample Schema

Last year we launched a new sample schema, Customer Orders. We've now extended this schema to include shipping and stock details.

What Have We Changed?

The schema includes two new tables:

• shipments
• inventory

The complete schema is now:

Why Have We Added These Tables?

When creating Customer Orders, we always envisaged we would extend the schema as new use cases arose.

In this case we wanted to more many-to-many (M:M) relationships in the schema. This opens up more complex query possibilities, such as find all the stores with no or low stock for a product.

It also helps highlight the challenges with representing M:M relationships in JSON documents. For example, if you want to show the stock available for each product in each store, you need to decide:

• Do you list the stores with an array of products under each, or the other way around?
• If you choose stores as the parent entity, do you repeat all the product details for each, leading to lots of duplication? Or store just the product identifiers, meaning you have to look them up later?
• If stores are the parent entity, how do you represent products with no stock in any store in the document?

Where Can I Find the Changes?

You can get the latest version from the Oracle sample schemas GitHub repository.

How Do I Install the New Tables?

After downloading the scripts from GitHub, you can rebuild the schema by running:

This will drop and recreate the user CO.

@co_main <CO_password> <connect string> <tablespace> <temp tablespace>

For example:

@co_main copassword localhost:1521/pdb USERS TEMP

If you want to re-create the tables in an existing schema, connect to it and run:

@co_ddl
@co_dml

If you want to add these tables to an existing installation, you can run these commands when connected as the table owner:

create table shipments (
  shipment_id          integer 
                       generated by default on null as identity,
  store_id             integer not null,
  customer_id          integer not null,
  delivery_address     varchar2(512 char) not null,
  shipment_status      varchar2(100 char) not null)
;

create table inventory (
  inventory_id          integer
                        generated by default on null as identity,
  store_id              integer not null,
  product_id            integer not null,
  product_inventory     integer not null)
;

alter table order_items add (
  shipment_id integer
);

create index shipments_store_id_i on shipments ( store_id );
create index shipments_customer_id_i on shipments ( customer_id );
create index inventory_product_id_i on inventory ( product_id );

alter table inventory add constraint inventory_pk primary key (inventory_id);

alter table inventory add constraint inventory_store_product_u unique (store_id, product_id);

alter table inventory add constraint inventory_store_id_fk 
   foreign key (store_id) references stores (store_id);

alter table inventory add constraint shipments_product_id_fk 
   foreign key (product_id) references products (product_id);
   
alter table shipments add constraint shipments_pk primary key (shipment_id);

alter table shipments add constraint shipments_store_id_fk 
   foreign key (store_id) references stores (store_id);

alter table shipments add constraint shipments_customer_id_fk 
   foreign key (customer_id) references customers (customer_id);
                  
alter table shipments add constraint shipment_status_c
                  check ( shipment_status in 
                    ( 'CREATED', 'SHIPPED', 'IN-TRANSIT', 'DELIVERED'));
   
alter table order_items add constraint order_items_shipment_id_fk
   foreign key (shipment_id) references shipments (shipment_id);

comment on column order_items.shipment_id 
  is 'Where this product will be delivered';
  
comment on table shipments
  is 'Details of where ordered goods will be delivered';
  
comment on column shipments.shipment_id
  is 'Auto-incrementing primary key';
  
comment on column shipments.store_id
  is 'Which location the goods will be transported from';
  
comment on column shipments.customer_id
  is 'Who this shipment is for';
  
comment on column shipments.delivery_address
  is 'Where the goods will be transported to';
  
comment on column shipments.shipment_status
  is 'The current status of the shipment. Valid values are: 
CREATED - the shipment is ready for order assignment
SHIPPED - the goods have been dispatched
IN-TRANSIT - the goods are en-route to their destination
DELIVERED - the good have arrived at their destination';

comment on table inventory
  is 'Details of the quantity of stock available for products at each location';
  
comment on column inventory.inventory_id
  is 'Auto-incrementing primary key';
  
comment on column inventory.store_id
  is 'Which location the goods are located at';
  
comment on column inventory.product_id
  is 'Which item this stock is for';
  
comment on column inventory.product_inventory
  is 'The current quantity in stock';

Populate these by running the co_dml script in the repository.

What Can I Do with These Tables?

Here are some sample queries to help you get familiar with the new tables and get your SQL juices flowing.

Find All the Stores with No Stock for a Product

select store_name from co.stores s
where  not exists (
  select * from co.inventory i
  where  s.store_id = i.store_id
  and    i.product_id = 9
  and    i.product_inventory > 0
); 

Show All Products Ordered Online Where the Outstanding Quantity to Ship Is Greater than the Stock Available

with quantity_to_ship as (
  select store_id, product_id, sum ( quantity ) ordered_quantity
  from   co.orders orde
  join   co.order_items orit
  using  ( order_id )
  where  orit.shipment_id is null
  and    store_id = 1
  and    order_status <> 'CANCELLED'
  group  by store_id, product_id
)
  select product_id, ordered_quantity, product_inventory
  from   quantity_to_ship
  join   co.inventory
  using  ( store_id, product_id )
  where  ordered_quantity > product_inventory;

Return the Three Most Recent Delivered Orders for a Customer as JSON

select json_object (
         'orderDatetime' value orde.order_datetime,
         'orderTotal' value sum ( orit.quantity * orit.unit_price ),
         'items' value json_arrayagg (
           json_object (
             'productId' value orit.product_id,
             'quantity'  value orit.quantity,
             'unitPrice' value orit.unit_price,
             'total'     value orit.quantity * orit.unit_price
           )
         )
       )
from   co.shipments ship
join   co.order_items orit
on     orit.shipment_id = ship.shipment_id
join   co.orders orde
on     orit.order_id = orde.order_id
where  shipment_status = 'DELIVERED'
and    ship.customer_id = 1
group  by orde.order_id, orde.order_datetime
order  by orde.order_datetime desc
fetch  first 3 rows only;  

We'd love to hear what you're using this schema for. Let us know in the comments!