webshop_schema.sql

--- DROP TABLE IF EXISTS customer, product, payment_method, "order", order_item CASCADE ;

CREATE TABLE customer (
    customer_id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    name TEXT NOT NULL,
    dob DATE NOT NULL,
    email TEXT UNIQUE NOT NULL,
    created_on TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE product (
    product_id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    name TEXT NOT NULL,
    list_price NUMERIC(10,2) NOT NULL -- before discounts
);

CREATE TABLE payment_method (
    payment_method_id INT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    method_name TEXT NOT NULL UNIQUE
);

CREATE TABLE "order" (
    order_id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    customer_id INT NOT NULL REFERENCES customer(customer_id),
    payment_method_id INT NOT NULL REFERENCES payment_method(payment_method_id),
    discount_pct NUMERIC(3),
    has_been_delivered BOOL NOT NULL DEFAULT false,
    created_on TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order_item (
    id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    order_id BIGINT NOT NULL REFERENCES "order"(order_id),
    product_id BIGINT NOT NULL REFERENCES product(product_id),
    price_after_discounts NUMERIC(10,2) NOT NULL
);