Skip to content

Instantly share code, notes, and snippets.

@ralfbecher

ralfbecher/orionbelt_1_DDL_pg.sql

Created September 8, 2025 10:05
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save ralfbecher/676639961165c6f6209ae97d2e314cdd to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save ralfbecher/676639961165c6f6209ae97d2e314cdd to your computer and use it in GitHub Desktop.
Download ZIP
Generate Test Data Schema
Raw
orionbelt_1_DDL_pg.sql
-- create schema orionbelt_1;
set schema 'orionbelt_1'; -- ensures that you work on this schema, and not on the default public
-- tables summary:
drop table if exists clients cascade;
drop table if exists products cascade;
drop table if exists sales cascade;
drop table if exists shipments cascade;
drop table if exists purchases cascade;
drop table if exists returns cascade;
drop table if exists employees cascade;
drop table if exists suppliers cascade;
drop table if exists channels cascade;
drop table if exists countries cascade;
drop table if exists regions cascade;
drop table if exists clientcomplaints cascade;
drop table if exists calendar cascade;
drop table if exists acctbal cascade;
drop table if exists banks cascade;
----------------------------------------------------------------------------------------------------
-- clients
----------------------------------------------------------------------------------------------------
create table clients (
clientid text,
clientname text,
clientgender text,
clientemail text,
clientcountryid text
);
----------------------------------------------------------------------------------------------------
-- products
----------------------------------------------------------------------------------------------------
create table products (
productid text,
productname text,
productcat text,
productsuppl text,
unitcost float,
unitprice float,
curr text,
unitsinstock float
);
----------------------------------------------------------------------------------------------------
-- sales
----------------------------------------------------------------------------------------------------
create table sales (
salesid text,
salesdate date,
salesclient text,
product text,
salesempid text,
salespaymenttype text,
saleschanid text,
salesquantity float,
salesamount float,
notes text
);
----------------------------------------------------------------------------------------------------
-- shipments
----------------------------------------------------------------------------------------------------
create table shipments (
shipmentid text,
shipmentsalesid text,
shipmentdate date,
shipmentclient text,
shipmentproduct text,
shipmentempid text,
shipmenttype text,
shipmentquantity float,
shipmentamount float,
notes text
);
----------------------------------------------------------------------------------------------------
-- purchases
----------------------------------------------------------------------------------------------------
create table purchases (
purchaseid text,
purchasedate date,
purchaseproduct text,
purchaseempid text,
purchasesupplier text,
purchasechanid text,
purchasequantity float,
purchaseprice float,
purchaseamount float,
notes text
);
----------------------------------------------------------------------------------------------------
-- returns
----------------------------------------------------------------------------------------------------
create table returns (
returnid text,
returnsalesid text,
returndate date,
returnempid text,
returnquantity float,
returnamount float,
notes text
);
----------------------------------------------------------------------------------------------------
-- employees
----------------------------------------------------------------------------------------------------
create table employees (
employeeid text,
employeename text,
department text
);
----------------------------------------------------------------------------------------------------
-- suppliers
----------------------------------------------------------------------------------------------------
create table suppliers (
supplierid text,
suppliername text,
suppliercountryid text
);
----------------------------------------------------------------------------------------------------
-- channels
----------------------------------------------------------------------------------------------------
create table channels (
channelid text, channelname text)
;
----------------------------------------------------------------------------------------------------
-- countries
----------------------------------------------------------------------------------------------------
create table countries (
countryid text,
countryname text,
region text
);
----------------------------------------------------------------------------------------------------
-- regions
----------------------------------------------------------------------------------------------------
create table regions (
regionid text,
regionname text
);
----------------------------------------------------------------------------------------------------
-- calendar (to be added)
----------------------------------------------------------------------------------------------------
create table calendar (
date date,
ym text,
weekday text,
publicholiday text
);
----------------------------------------------------------------------------------------------------
-- clientcomplaints
----------------------------------------------------------------------------------------------------
create table clientcomplaints (
complid text,
complclientid text,
compltext text
);
----------------------------------------------------------------------------------------------------
-- acctbal
----------------------------------------------------------------------------------------------------
create table acctbal (
accountid text,
iban text,
bankid text,
balanceamt float
);
----------------------------------------------------------------------------------------------------
-- banks
----------------------------------------------------------------------------------------------------
create table banks (
bankid text,
bankname text
);
----------------------------------------------------------------------------------------------------
-- pks uss_1
----------------------------------------------------------------------------------------------------
alter table clients add primary key (clientid);
alter table products add primary key (productid);
alter table sales add primary key (salesid);
alter table shipments add primary key (shipmentid);
alter table purchases add primary key (purchaseid);
alter table returns add primary key (returnid);
alter table employees add primary key (employeeid);
alter table suppliers add primary key (supplierid);
alter table channels add primary key (channelid);
alter table countries add primary key (countryid);
alter table regions add primary key (regionid);
alter table clientcomplaints add primary key (complid);
alter table calendar add primary key (date);
alter table acctbal add primary key (accountid);
alter table banks add primary key (bankid);
----------------------------------------------------------------------------------------------------
-- fks uss_1
----------------------------------------------------------------------------------------------------
alter table clients add foreign key (clientcountryid) references countries(countryid);
alter table products add foreign key (productsuppl) references suppliers(supplierid);
alter table countries add foreign key (region) references regions(regionid);
alter table sales add foreign key (salesclient) references clients(clientid);
alter table sales add foreign key (product) references products(productid);
alter table sales add foreign key (salesempid) references employees(employeeid);
alter table sales add foreign key (saleschanid) references channels(channelid);
alter table sales add foreign key (salesdate) references calendar(date);
alter table shipments add foreign key (shipmentsalesid) references sales(salesid);
alter table shipments add foreign key (shipmentempid) references employees(employeeid);
alter table shipments add foreign key (shipmentdate) references calendar(date);
alter table purchases add foreign key (purchaseproduct) references products(productid);
alter table purchases add foreign key (purchasechanid) references channels(channelid);
alter table purchases add foreign key (purchaseempid) references employees(employeeid);
alter table purchases add foreign key (purchasedate) references calendar(date);
alter table returns add foreign key (returnsalesid) references sales(salesid);
alter table returns add foreign key (returnempid) references employees(employeeid);
alter table returns add foreign key (returndate) references calendar(date);
alter table acctbal add foreign key (bankid) references banks(bankid);
alter table clientcomplaints add foreign key (complclientid) references clients(clientid);
----------------------------------------------------------------------------------------------------
-- END OF SCRIPT
----------------------------------------------------------------------------------------------------
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment