124 lines
No EOL
4 KiB
SQL
124 lines
No EOL
4 KiB
SQL
PRAGMA foreign_keys = OFF;
|
|
|
|
-- Drop everything...
|
|
DROP TABLE IF EXISTS pallets;
|
|
DROP TABLE IF EXISTS raw_materials_deliveries;
|
|
DROP TABLE IF EXISTS raw_materials;
|
|
DROP TABLE IF EXISTS orders;
|
|
DROP TABLE IF EXISTS customers;
|
|
DROP TABLE IF EXISTS cookies;
|
|
|
|
--------------------------------------------
|
|
-- Orders, deliveries and customers
|
|
--------------------------------------------
|
|
|
|
-- Our known customers, may need more fields
|
|
CREATE TABLE IF NOT EXISTS customers (
|
|
customer_id INTEGER PRIMARY KEY,
|
|
customer_name VARCHAR(50) NOT NULL,
|
|
customer_address VARCHAR(50) NOT NULL
|
|
);
|
|
|
|
-- Orders from customers.
|
|
CREATE TABLE IF NOT EXISTS orders (
|
|
order_id INTEGER PRIMARY KEY,
|
|
customer_id INT NOT NULL,
|
|
order_date DATE NOT NULL DEFAULT CURRENT_DATE CHECK (order_date >= CURRENT_DATE),
|
|
expected_delivery_date DATE NOT NULL,
|
|
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS order_spec (
|
|
nbr_pallets INTEGER NOT NULL,
|
|
order_id INTEGER NOT NULL,
|
|
cookie_id INTEGER NOT NULL,
|
|
FOREIGN KEY (order_id) REFERENCES orders(order_id),
|
|
FOREIGN KEY (cookie_id) REFERENCES cookies(cookie_id),
|
|
PRIMARY KEY (order_id, cookie_id)
|
|
);
|
|
|
|
--------------------------------------------
|
|
-- Cookies, raw_materials and recipes
|
|
--------------------------------------------
|
|
-- Notes: the unit type can be defined in terms
|
|
-- of volume or weight instead. Here we choose
|
|
-- to use static si-prefixes in relevant tables.
|
|
|
|
-- Holds the different types of cookies we can make.
|
|
CREATE TABLE IF NOT EXISTS cookies (
|
|
cookie_id INTEGER PRIMARY KEY,
|
|
cookie_name VARCHAR(50) NOT NULL UNIQUE
|
|
);
|
|
|
|
-- What types of raw_materials do we handle.
|
|
-- raw_materials quantity tells us amount in stock
|
|
CREATE TABLE IF NOT EXISTS raw_materials (
|
|
ingredient_id INTEGER PRIMARY KEY,
|
|
ingredient_name VARCHAR(50) NOT NULL UNIQUE,
|
|
ingredient_quantity INT NOT NULL,
|
|
unit VARCHAR(50) NOT NULL CHECK (unit IN ('g', 'ml'))
|
|
);
|
|
|
|
-- What raw_materials are in what cookies?
|
|
-- Glues together the cookies and raw_materials, a 'recipe'.
|
|
CREATE TABLE IF NOT EXISTS recipe_contents (
|
|
cookie_id INT NOT NULL,
|
|
ingredient_id INT NOT NULL,
|
|
quantity INT NOT NULL,
|
|
unit VARCHAR(50) NOT NULL CHECK (unit IN ('g', 'ml')),
|
|
PRIMARY KEY (cookie_id, ingredient_id),
|
|
FOREIGN KEY (cookie_id) REFERENCES cookies(cookie_id),
|
|
FOREIGN KEY (ingredient_id) REFERENCES raw_materials(ingredient_id)
|
|
);
|
|
|
|
-- When did we get the raw_materials?
|
|
CREATE TABLE IF NOT EXISTS raw_materials_deliveries (
|
|
delivery_id INTEGER PRIMARY KEY,
|
|
ingredient_id INT NOT NULL,
|
|
delivery_date DATE NOT NULL,
|
|
delivery_quantity INT NOT NULL,
|
|
unit VARCHAR(50) NOT NULL CHECK (unit IN ('g', 'ml')),
|
|
FOREIGN KEY (ingredient_id) REFERENCES raw_materials(ingredient_id)
|
|
);
|
|
|
|
--------------------------------------------
|
|
-- Pallet related tables
|
|
--------------------------------------------
|
|
|
|
-- Pallets are used to store cookies for delivery
|
|
-- Order related columns are unused for now.
|
|
CREATE TABLE IF NOT EXISTS pallets (
|
|
pallet_id INTEGER PRIMARY KEY,
|
|
cookie_id INT NOT NULL,
|
|
status VARCHAR(50) NOT NULL CHECK (status IN ('freezer', 'delivered', 'blocked')),
|
|
production_date DATE NOT NULL DEFAULT NOW,
|
|
FOREIGN KEY (cookie_id) REFERENCES cookies(cookie_id)
|
|
);
|
|
|
|
-- Connects pallets to orders
|
|
CREATE TABLE IF NOT EXISTS deliveries (
|
|
delivery_date DATE DEFAULT NOW,
|
|
order_id INT NOT NULL,
|
|
pallet_id INT NOT NULL,
|
|
FOREIGN KEY (order_id) REFERENCES orders(order_id),
|
|
FOREIGN KEY (pallet_id) REFERENCES pallets(pallet_id),
|
|
PRIMARY KEY (order_id, pallet_id)
|
|
);
|
|
|
|
--------------------------------------------
|
|
-- Views
|
|
--------------------------------------------
|
|
|
|
-- Pallet
|
|
CREATE VIEW IF NOT EXISTS pallets_view AS
|
|
SELECT
|
|
pallets.pallet_id,
|
|
cookie_name,
|
|
status,
|
|
production_date,
|
|
delivery_date
|
|
FROM pallets
|
|
LEFT JOIN cookies ON pallets.cookie_id = cookies.cookie_id
|
|
LEFT JOIN deliveries ON pallets.pallet_id = deliveries.pallet_id;
|
|
|
|
PRAGMA foreign_keys = ON; |