Last year we launched a new sample schema, Customer Orders. We've now extended this schema to include shipping and stock details.
The schema includes two new tables:
The complete schema is now:
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:
You can get the latest version from the Oracle sample schemas GitHub repository.
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>
@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:
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.
Here are some sample queries to help you get familiar with the new tables and get your SQL juices flowing.
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 );
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;
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!