Changes between Version 1 and Version 2 of ERModel

Timestamp:

04/21/26 22:14:38 (11 days ago)

Author:

185022

Comment:

--

ERModel

@@ -7 +7 @@
 
 **ADMIN**
-* user_id INTEGER PRIMARY KEY DEFAULT 1,
+* user_id int, примарен клуч DEFAULT 1
 * CONSTRAINT fk_admin_user
         FOREIGN KEY (user_id)
@@ -14 +14 @@
         ON DELETE SET DEFAULT
 
-**ALERGEN**
-* alergen_id SERIAL PRIMARY KEY,
-* alergen_name VARCHAR(255) NOT NULL UNIQUE,
-* alergen_description VARCHAR(255)
+**ALLERGEN**
+* allergen_id serial, примарен клуч
+* allergen_name string, задолжителен и уникатен
+* allergen_description string
 
 **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
+* user_id serial, примарен клуч
+* user_first_name string, задолжителен
+* user_last_name string, задолжителен
+* user_email string, задолжителен и уникатен
+* user_phone_no string, уникатен
+* user_password_hash TEXT, задолжителен
 
 **CATEGORY**
-* cat_id SERIAL PRIMARY KEY,
-* cat_name VARCHAR(255) NOT NULL UNIQUE,
-* cat_description VARCHAR(255)
+* cat_id serial, примарен клуч
+* cat_name string, задолжителен и уникатен
+* cat_description string
 
 **COMPANY**
-* company_id SERIAL PRIMARY KEY,
-* company_name VARCHAR(255) NOT NULL UNIQUE,
-* company_address VARCHAR(255) NOT NULL
+* company_id serial, примарен клуч
+* company_name string, задолжителен и уникатен
+* company_address string, задолжителен
 
 **COMPANY_ORDER**
-* comp_order_id SERIAL PRIMARY KEY,
-* company_id INTEGER NOT NULL,
-* delivery_id INTEGER UNIQUE,
+* comp_order_id serial, примарен клуч
+* company_id int, задолжителен
+* delivery_id int, уникатен
 * CONSTRAINT fk_company_order_company
     FOREIGN KEY (company_id)
     REFERENCES company(company_id)
     ON UPDATE CASCADE
-    ON DELETE RESTRICT,
+    ON DELETE RESTRICT
 * CONSTRAINT fk_company_order_delivery
     FOREIGN KEY (delivery_id)
@@ -53 +53 @@
 
 **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,
+* contract_id serial, примарен клуч
+* company_id int, задолжителен
+* rest_id int, задолжителен
+* contract_status_id int, задолжителен
+* contract_start_date date, задолжителен
+* contract_end_date date, задолжителен
 * CONSTRAINT fk_contract_company
     FOREIGN KEY (company_id)
     REFERENCES company(company_id)
     ON UPDATE CASCADE
-    ON DELETE RESTRICT,
+    ON DELETE RESTRICT
 * CONSTRAINT fk_contract_restaurant
     FOREIGN KEY (rest_id)
     REFERENCES restaurant(rest_id)
     ON UPDATE CASCADE
-    ON DELETE RESTRICT,
+    ON DELETE RESTRICT
 * CONSTRAINT fk_contract_status
     FOREIGN KEY (contract_status_id)
     REFERENCES contract_status(contract_status_id)
     ON UPDATE CASCADE
-    ON DELETE RESTRICT,
+    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
+* contract_status_id serial, примарен клуч
+* contract_status_name string, задолжителен и уникатен
 
 **CUSTOMER**
-*  user_id INTEGER PRIMARY KEY DEFAULT 1,
+*  user_id int PRIMARY KEY DEFAULT 1
 * CONSTRAINT fk_customer_user
     FOREIGN KEY (user_id)
@@ -89 +89 @@
 
 **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,
+* order_id serial, примарен клуч
+* comp_order_id int, задолжителен
+* company_employee_user_id int, задолжителен DEFAULT 1
+* order_datetime timestamp, задолжителен DEFAULT CURRENT_TIMESTAMP
+* o_status_id int, задолжителен
+* order_total NUMERIC(10,2), задолжителен
 * CONSTRAINT fk_customer_order_company_order
     FOREIGN KEY (comp_order_id)
     REFERENCES company_order(comp_order_id)
     ON UPDATE CASCADE
-    ON DELETE RESTRICT,
+    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,
+    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,
+    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,
+* delivery_id serial, примарен клуч
+* delivery_date DATE, задолжителен DEFAULT CURRENT_DATE
+* delivery_notes string
+* d_status_id int, задолжителен
+* driver_user_id int, задолжителен DEFAULT 1
 * CONSTRAINT fk_delivery_status
     FOREIGN KEY (d_status_id)
     REFERENCES delivery_status(d_status_id)
     ON UPDATE CASCADE
-    ON DELETE RESTRICT,
+    ON DELETE RESTRICT
 * CONSTRAINT fk_delivery_driver
     FOREIGN KEY (driver_user_id)
@@ -130 +130 @@
 
 **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,
+* delivery_review_id serial, примарен клуч
+* delivery_id int, задолжителен и уникатен
+* del_review_courier_rating int, задолжителен
+* del_review_speed_rating int, задолжителен
 * 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),
+        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
+* d_status_id serial, примарен клуч
+* d_status_name string, задолжителен и уникатен
 
 **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,
+* drink_id serial, примарен клуч
+* rest_id int, задолжителен
+* drink_name string, задолжителен
+* drink_milliliters int, задолжителен
+* drink_price NUMERIC(10,2), задолжителен
 * 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),
+    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,
+* user_id int PRIMARY KEY DEFAULT 1
 * CONSTRAINT fk_driver_user
     FOREIGN KEY (user_id)
@@ -170 +170 @@
 
 **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,
+* emp_loyalty_id serial, примарен клуч
+* user_id int, задолжителен и уникатен
+* emp_loyalty_curr_points int, задолжителен DEFAULT 0
+* emp_loyalty_joined_at timestamp, задолжителен DEFAULT CURRENT_TIMESTAMP
+* emp_loyalty_status_id int, задолжителен
+* tier_id int, задолжителен
 * CONSTRAINT fk_employee_loyalty_user
     FOREIGN KEY (user_id)
     REFERENCES company_employee(user_id)
     ON UPDATE CASCADE
-    ON DELETE 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,
+    ON DELETE RESTRICT
 * CONSTRAINT fk_employee_loyalty_tier
     FOREIGN KEY (tier_id)
     REFERENCES loyalty_tier(tier_id)
     ON UPDATE CASCADE
-    ON DELETE RESTRICT,
+    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
+* emp_loyalty_status_id serial, примарен клуч
+* emp_loyalty_status_name string, задолжителен и уникатен
 
 **INGREDIENT**
-* ingr_id SERIAL PRIMARY KEY,
-* ingr_name VARCHAR(255) NOT NULL UNIQUE
+* ingr_id serial, примарен клуч
+* ingr_name string, задолжителен и уникатен
 
 **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),
+* tier_id serial, примарен клуч
+* tier_name string, задолжителен и уникатен
+* tier_minimum_points int, задолжителен
+* tier_maximum_points int, задолжителен
+* tier_discount_percentage NUMERIC(5,2), задолжителен
+* tier_free_delivery_eligibility bool, задолжителен DEFAULT FALSE
+* tier_priority_support bool, задолжителен DEFAULT FALSE
+* tier_created_at DATE, задолжителен DEFAULT CURRENT_DATE
+* tier_update_at timestamp, задолжителен 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,
+* lunch_time_id serial, примарен клуч
+* comp_order_id int
+* contract_id int, задолжителен
+* lunch_start timestamp, задолжителен
+* lunch_end timestamp, задолжителен
+* lunch_preorder_offset int, задолжителен DEFAULT 0
+* lunch_weekday string, задолжителен
 * CONSTRAINT fk_lunch_time_company_order
     FOREIGN KEY (comp_order_id)
     REFERENCES company_order(comp_order_id)
     ON UPDATE CASCADE
-    ON DELETE SET NULL,
+    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),
+    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,
+* meal_id serial, примарен клуч
+* cat_id int, задолжителен
+* rest_id int, задолжителен
+* meal_name string, задолжителен
+* meal_description string
+* meal_price NUMERIC(10,2), задолжителен
+* meal_weight int, задолжителен
 * CONSTRAINT fk_meal_category
     FOREIGN KEY (cat_id)
     REFERENCES category(cat_id)
     ON UPDATE CASCADE
-    ON DELETE RESTRICT,
+    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),
+    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,
+* order_review_id serial, примарен клуч
+* order_id int, задолжителен и уникатен
+* order_review_food_rating int, задолжителен
+* order_review_res_rating int, задолжителен
 * 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),
+    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
+* o_status_id serial, примарен клуч
+* o_status_name string, задолжителен и уникатен
 
 **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
+* rest_id serial, примарен клуч
+* rest_name string, задолжителен
+* rest_email string, задолжителен и уникатен
+* rest_location string, задолжителен
+* rest_phone string, задолжителен и уникатен
+* rest_website string, уникатен
 
 **REVIEW**
-* review_id SERIAL PRIMARY KEY,
-* review_comment VARCHAR(255),
-* review_created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
-* review_rating INTEGER NOT NULL,
+* review_id serial, примарен клуч
+* review_comment string
+* review_created_at timestamp, задолжителен DEFAULT CURRENT_TIMESTAMP
+* review_rating int, задолжителен
 * CONSTRAINT chk_review_rating CHECK (review_rating BETWEEN 1 AND 5)
+
+==== Релациски табели
+
+**Order_Meal**
+* order_id int, задолжителен
+* meal_id int, задолжителен
+* quantity int, задолжителен DEFAULT 1
+* PRIMARY KEY (order_id, meal_id), примарен клуч
+* CONSTRAINT fk_order_meal_order
+    FOREIGN KEY (order_id)
+    REFERENCES customer_order(order_id)
+    ON UPDATE CASCADE
+    ON DELETE CASCADE
+* CONSTRAINT fk_order_meal_meal
+    FOREIGN KEY (meal_id)
+    REFERENCES meal(meal_id)
+    ON UPDATE CASCADE
+    ON DELETE RESTRICT
+* CONSTRAINT chk_order_meal_quantity CHECK (quantity > 0)
+
+**Order_Drink**
+* order_id int, задолжителен
+* drink_id int, задолжителен
+* quantity int, задолжителен DEFAULT 1
+* PRIMARY KEY (order_id, drink_id), примарен клуч
+* CONSTRAINT fk_order_drink_order
+    FOREIGN KEY (order_id)
+    REFERENCES customer_order(order_id)
+    ON UPDATE CASCADE
+    ON DELETE CASCADE
+* CONSTRAINT fk_order_drink_drink
+    FOREIGN KEY (drink_id)
+    REFERENCES drink(drink_id)
+    ON UPDATE CASCADE
+    ON DELETE RESTRICT
+* CONSTRAINT chk_order_drink_quantity CHECK (quantity > 0)
+
+**Meal_Ingredient**
+* meal_id int, задолжителен
+* ingr_id int, задолжителен
+* PRIMARY KEY (meal_id, ingr_id), примарен клуч
+* CONSTRAINT fk_meal_ingredient_meal
+    FOREIGN KEY (meal_id)
+    REFERENCES meal(meal_id)
+    ON UPDATE CASCADE
+    ON DELETE CASCADE
+* CONSTRAINT fk_meal_ingredient_ingredient
+    FOREIGN KEY (ingr_id)
+    REFERENCES ingredient(ingr_id)
+    ON UPDATE CASCADE
+    ON DELETE CASCADE
+
+**Allergen_Ingredient**
+* allergen_id int, задолжителен
+* ingr_id int, задолжителен
+* PRIMARY KEY (allergen_id, ingr_id), примарен клуч
+* CONSTRAINT fk_allergen_ingredient_allergen
+    FOREIGN KEY (allergen_id)
+    REFERENCES allergen(allergen_id)
+    ON UPDATE CASCADE
+    ON DELETE CASCADE
+* CONSTRAINT fk_allergen_ingredient_ingredient
+    FOREIGN KEY (ingr_id)
+    REFERENCES ingredient(ingr_id)
+    ON UPDATE CASCADE
+    ON DELETE CASCADE