Changes between Initial Version and Version 1 of ERModel

Timestamp:

04/20/26 17:49:00 (12 days ago)

Author:

185022

Comment:

--

ERModel

@@ +1 @@
+== ЕР Дијаграм
+[[Image(kbnteam_ERDiagram.jpg)]]
+== Податочни побарувања
+=== Ентитети
+
+
+
+**ADMIN**
+* user_id INTEGER PRIMARY KEY DEFAULT 1,
+* CONSTRAINT fk_admin_user
+        FOREIGN KEY (user_id)
+        REFERENCES api_user(user_id)
+        ON UPDATE CASCADE
+        ON DELETE SET DEFAULT
+
+**ALERGEN**
+* alergen_id SERIAL PRIMARY KEY,
+* alergen_name VARCHAR(255) NOT NULL UNIQUE,
+* alergen_description VARCHAR(255)
+
+**API_USER**
+* user_id SERIAL PRIMARY KEY,
+* user_first_name VARCHAR(255) NOT NULL,
+* user_last_name VARCHAR(255) NOT NULL,
+* user_email VARCHAR(255) NOT NULL UNIQUE,
+* user_phone_no VARCHAR(20) UNIQUE,
+* user_password_hash TEXT NOT NULL
+
+**CATEGORY**
+* cat_id SERIAL PRIMARY KEY,
+* cat_name VARCHAR(255) NOT NULL UNIQUE,
+* cat_description VARCHAR(255)
+
+**COMPANY**
+* company_id SERIAL PRIMARY KEY,
+* company_name VARCHAR(255) NOT NULL UNIQUE,
+* company_address VARCHAR(255) NOT NULL
+
+**COMPANY_ORDER**
+* comp_order_id SERIAL PRIMARY KEY,
+* company_id INTEGER NOT NULL,
+* delivery_id INTEGER UNIQUE,
+* CONSTRAINT fk_company_order_company
+    FOREIGN KEY (company_id)
+    REFERENCES company(company_id)
+    ON UPDATE CASCADE
+    ON DELETE RESTRICT,
+* CONSTRAINT fk_company_order_delivery
+    FOREIGN KEY (delivery_id)
+    REFERENCES delivery(delivery_id)
+    ON UPDATE CASCADE
+    ON DELETE SET NULL
+
+**CONTRACT**
+* contract_id SERIAL PRIMARY KEY,
+* company_id INTEGER NOT NULL,
+* rest_id INTEGER NOT NULL,
+* contract_status_id INTEGER NOT NULL,
+* contract_start_date DATE NOT NULL,
+* contract_end_date DATE NOT NULL,
+* CONSTRAINT fk_contract_company
+    FOREIGN KEY (company_id)
+    REFERENCES company(company_id)
+    ON UPDATE CASCADE
+    ON DELETE RESTRICT,
+* CONSTRAINT fk_contract_restaurant
+    FOREIGN KEY (rest_id)
+    REFERENCES restaurant(rest_id)
+    ON UPDATE CASCADE
+    ON DELETE RESTRICT,
+* CONSTRAINT fk_contract_status
+    FOREIGN KEY (contract_status_id)
+    REFERENCES contract_status(contract_status_id)
+    ON UPDATE CASCADE
+    ON DELETE RESTRICT,
+* CONSTRAINT chk_contract_dates CHECK (contract_end_date >= contract_start_date)
+
+**CONTRACT_STATUS**
+* contract_status_id SERIAL PRIMARY KEY,
+* contract_status_name VARCHAR(255) NOT NULL UNIQUE
+
+**CUSTOMER**
+*  user_id INTEGER PRIMARY KEY DEFAULT 1,
+* CONSTRAINT fk_customer_user
+    FOREIGN KEY (user_id)
+    REFERENCES api_user(user_id)
+    ON UPDATE CASCADE
+    ON DELETE SET DEFAULT
+
+**CUSTOMER_ORDER**
+* order_id SERIAL PRIMARY KEY,
+* comp_order_id INTEGER NOT NULL,
+* company_employee_user_id INTEGER NOT NULL DEFAULT 1,
+* order_datetime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+* o_status_id INTEGER NOT NULL,
+* order_total NUMERIC(10,2) NOT NULL,
+* CONSTRAINT fk_customer_order_company_order
+    FOREIGN KEY (comp_order_id)
+    REFERENCES company_order(comp_order_id)
+    ON UPDATE CASCADE
+    ON DELETE RESTRICT,
+* CONSTRAINT fk_customer_order_employee
+    FOREIGN KEY (company_employee_user_id)
+    REFERENCES company_employee(user_id)
+    ON UPDATE CASCADE
+    ON DELETE SET DEFAULT,
+* CONSTRAINT fk_customer_order_status
+    FOREIGN KEY (o_status_id)
+    REFERENCES order_status(o_status_id)
+    ON UPDATE CASCADE
+    ON DELETE RESTRICT,
+* CONSTRAINT chk_order_total CHECK (order_total >= 0)
+    
+**DELIVERY**
+* delivery_id SERIAL PRIMARY KEY,
+* delivery_date DATE NOT NULL DEFAULT CURRENT_DATE,
+* delivery_notes VARCHAR(255),
+* d_status_id INTEGER NOT NULL,
+* driver_user_id INTEGER NOT NULL DEFAULT 1,
+* CONSTRAINT fk_delivery_status
+    FOREIGN KEY (d_status_id)
+    REFERENCES delivery_status(d_status_id)
+    ON UPDATE CASCADE
+    ON DELETE RESTRICT,
+* CONSTRAINT fk_delivery_driver
+    FOREIGN KEY (driver_user_id)
+    REFERENCES driver(user_id)
+    ON UPDATE CASCADE
+    ON DELETE SET DEFAULT
+
+**DELIVERY_REVIEW**
+* delivery_review_id SERIAL PRIMARY KEY,
+* delivery_id INTEGER NOT NULL UNIQUE,
+* del_review_courier_rating INTEGER NOT NULL,
+* del_review_speed_rating INTEGER NOT NULL,
+* CONSTRAINT fk_delivery_review_delivery
+        FOREIGN KEY (delivery_id)
+        REFERENCES delivery(delivery_id)
+        ON UPDATE CASCADE
+        ON DELETE CASCADE,
+* CONSTRAINT chk_delivery_review_courier_rating CHECK (del_review_courier_rating BETWEEN 1 AND 5),
+* CONSTRAINT chk_delivery_review_speed_rating CHECK (del_review_speed_rating BETWEEN 1 AND 5)
+
+**DELIVERY_STATUS**
+* d_status_id SERIAL PRIMARY KEY,
+* d_status_name VARCHAR(255) NOT NULL UNIQUE
+
+**DRINK**
+* drink_id SERIAL PRIMARY KEY,
+* rest_id INTEGER NOT NULL,
+* drink_name VARCHAR(255) NOT NULL,
+* drink_milliliters INTEGER NOT NULL,
+* drink_price NUMERIC(10,2) NOT NULL,
+* CONSTRAINT fk_drink_restaurant
+    FOREIGN KEY (rest_id)
+    REFERENCES restaurant(rest_id)
+    ON UPDATE CASCADE
+    ON DELETE RESTRICT,
+* CONSTRAINT chk_drink_milliliters CHECK (drink_milliliters > 0),
+* CONSTRAINT chk_drink_price CHECK (drink_price >= 0),
+* CONSTRAINT uq_drink_restaurant_name UNIQUE (rest_id, drink_name)
+
+**DRIVER**
+* user_id INTEGER PRIMARY KEY DEFAULT 1,
+* CONSTRAINT fk_driver_user
+    FOREIGN KEY (user_id)
+    REFERENCES api_user(user_id)
+    ON UPDATE CASCADE
+    ON DELETE SET DEFAULT
+
+**EMPLOYEE_LOYALTY**
+* emp_loyalty_id SERIAL PRIMARY KEY,
+* user_id INTEGER NOT NULL UNIQUE,
+* emp_loyalty_curr_points INTEGER NOT NULL DEFAULT 0,
+* emp_loyalty_joined_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+* emp_loyalty_status_id INTEGER NOT NULL,
+* tier_id INTEGER NOT NULL,
+* CONSTRAINT fk_employee_loyalty_user
+    FOREIGN KEY (user_id)
+    REFERENCES company_employee(user_id)
+    ON UPDATE CASCADE
+    ON DELETE CASCADE,
+* CONSTRAINT fk_employee_loyalty_status
+    FOREIGN KEY (emp_loyalty_status_id)
+    REFERENCES employee_loyalty_status(emp_loyalty_status_id)
+    ON UPDATE CASCADE
+    ON DELETE RESTRICT,
+* CONSTRAINT fk_employee_loyalty_tier
+    FOREIGN KEY (tier_id)
+    REFERENCES loyalty_tier(tier_id)
+    ON UPDATE CASCADE
+    ON DELETE RESTRICT,
+* CONSTRAINT chk_employee_loyalty_points CHECK (emp_loyalty_curr_points >= 0)
+
+**EMPLOYEE_LOYALTY_STATUS**
+* emp_loyalty_status_id SERIAL PRIMARY KEY,
+* emp_loyalty_status_name VARCHAR(255) NOT NULL UNIQUE
+
+**INGREDIENT**
+* ingr_id SERIAL PRIMARY KEY,
+* ingr_name VARCHAR(255) NOT NULL UNIQUE
+
+**LOYALTY_TIER**
+* tier_id SERIAL PRIMARY KEY,
+* tier_name VARCHAR(255) NOT NULL UNIQUE,
+* tier_minimum_points INTEGER NOT NULL,
+* tier_maximum_points INTEGER NOT NULL,
+* tier_discount_percentage NUMERIC(5,2) NOT NULL,
+* tier_free_delivery_eligibility BOOLEAN NOT NULL DEFAULT FALSE,
+* tier_priority_support BOOLEAN NOT NULL DEFAULT FALSE,
+* tier_created_at DATE NOT NULL DEFAULT CURRENT_DATE,
+* tier_update_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+* CONSTRAINT chk_tier_points_nonnegative CHECK (tier_minimum_points >= 0 AND tier_maximum_points >= 0),
+* CONSTRAINT chk_tier_points_range CHECK (tier_maximum_points >= tier_minimum_points),
+* CONSTRAINT chk_tier_discount CHECK (tier_discount_percentage >= 0 AND tier_discount_percentage <= 100)
+
+**LUNCH_TIME**
+* lunch_time_id SERIAL PRIMARY KEY,
+* comp_order_id INTEGER,
+* contract_id INTEGER NOT NULL,
+* lunch_start TIMESTAMP NOT NULL,
+* lunch_end TIMESTAMP NOT NULL,
+* lunch_preorder_offset INTEGER NOT NULL DEFAULT 0,
+* lunch_weekday VARCHAR(15) NOT NULL,
+* CONSTRAINT fk_lunch_time_company_order
+    FOREIGN KEY (comp_order_id)
+    REFERENCES company_order(comp_order_id)
+    ON UPDATE CASCADE
+    ON DELETE SET NULL,
+* CONSTRAINT fk_lunch_time_contract
+    FOREIGN KEY (contract_id)
+    REFERENCES contract(contract_id)
+    ON UPDATE CASCADE
+    ON DELETE CASCADE,
+* CONSTRAINT chk_lunch_time_offset CHECK (lunch_preorder_offset >= 0),
+* CONSTRAINT chk_lunch_weekday CHECK (
+    lunch_weekday IN ('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday')
+),
+* CONSTRAINT chk_lunch_interval CHECK (lunch_end > lunch_start)
+
+**MEAL**
+* meal_id SERIAL PRIMARY KEY,
+* cat_id INTEGER NOT NULL,
+* rest_id INTEGER NOT NULL,
+* meal_name VARCHAR(255) NOT NULL,
+* meal_description VARCHAR(255),
+* meal_price NUMERIC(10,2) NOT NULL,
+* meal_weight INTEGER NOT NULL,
+* CONSTRAINT fk_meal_category
+    FOREIGN KEY (cat_id)
+    REFERENCES category(cat_id)
+    ON UPDATE CASCADE
+    ON DELETE RESTRICT,
+* CONSTRAINT fk_meal_restaurant
+    FOREIGN KEY (rest_id)
+    REFERENCES restaurant(rest_id)
+    ON UPDATE CASCADE
+    ON DELETE RESTRICT,
+* CONSTRAINT chk_meal_price CHECK (meal_price >= 0),
+* CONSTRAINT chk_meal_weight CHECK (meal_weight > 0),
+* CONSTRAINT uq_meal_restaurant_name UNIQUE (rest_id, meal_name)
+
+**ORDER_REVIEW**
+* order_review_id SERIAL PRIMARY KEY,
+* order_id INTEGER NOT NULL UNIQUE,
+* order_review_food_rating INTEGER NOT NULL,
+* order_review_res_rating INTEGER NOT NULL,
+* CONSTRAINT fk_order_review_order
+    FOREIGN KEY (order_id)
+    REFERENCES customer_order(order_id)
+    ON UPDATE CASCADE
+    ON DELETE CASCADE,
+* CONSTRAINT chk_order_review_food_rating CHECK (order_review_food_rating BETWEEN 1 AND 5),
+* CONSTRAINT chk_order_review_res_rating CHECK (order_review_res_rating BETWEEN 1 AND 5)
+
+**ORDER_STATUS**
+* o_status_id SERIAL PRIMARY KEY,
+* o_status_name VARCHAR(255) NOT NULL UNIQUE
+
+**RESTAURANT**
+* rest_id SERIAL PRIMARY KEY,
+* rest_name VARCHAR(255) NOT NULL,
+* rest_email VARCHAR(255) NOT NULL UNIQUE,
+* rest_location VARCHAR(255) NOT NULL,
+* rest_phone VARCHAR(20) NOT NULL UNIQUE,
+* rest_website VARCHAR(255) UNIQUE
+
+**REVIEW**
+* review_id SERIAL PRIMARY KEY,
+* review_comment VARCHAR(255),
+* review_created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+* review_rating INTEGER NOT NULL,
+* CONSTRAINT chk_review_rating CHECK (review_rating BETWEEN 1 AND 5)