#Main Diagram
Generated using DbSchema

Main Diagram

img

Table company.contracts

Idx Name Data Type Description
* 🔑 contract_id INT This column contains private informations.
🔍 employee_id INT
contract_type VARCHAR(50)
🔎 ⬈ start_date DATE
end_date DATE
salary DECIMAL(10,2)
Indexes
Type Name On
🔑 pk_contracts ON contract_id
🔍 unq_contracts_employee_id ON employee_id
🔎 fk_contracts_salaries ON start_date
Foreign Keys
Type Name On
fk_contracts_salaries ( start_date ) ref company.salaries (effective_date)
Options

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Table company.customers

Idx Name Data Type
* 🔑 ⬋ customer_id INT
first_name VARCHAR(50)
last_name VARCHAR(50)
email VARCHAR(100)
phone_number VARCHAR(20)
address VARCHAR(255)
registration_date DATE
Indexes
Type Name On
🔑 pk_customers ON customer_id
Options

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Table company.employee_benefits

Idx Name Data Type
* 🔑 benefit_id INT
🔍 ⬋ employee_id INT
benefit_type VARCHAR(50)
start_date DATE
end_date DATE
Indexes
Type Name On
🔑 pk_employee_benefits ON benefit_id
🔍 unq_employee_benefits_employee_id ON employee_id
Options

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Table company.employee_projects

Idx Name Data Type
* 🔑 employee_id INT
* 🔑 ⬋ project_id INT
role VARCHAR(100)
start_date DATE
end_date DATE
Indexes
Type Name On
🔑 pk_employee_projects ON employee_id, project_id
🔍 unq_employee_projects_project_id ON project_id
Options

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Table company.employees

This field contains personal informations

Idx Name Data Type Description
* 🔑 ⬈ employee_id INT
🔍 first_name VARCHAR(50)
last_name VARCHAR(50)
email VARCHAR(100)
hire_date DATE
🔍 ⬈ salary DECIMAL(10,2) This column contains personal informations.
department_id INT
manager_id INT
Indexes
Type Name On
🔑 pk_employees ON employee_id
🔍 unq_employees_salary ON salary
🔍 unq_employees_first_name ON first_name
Foreign Keys
Type Name On
fk_employees_employee_benefits ( employee_id ) ref company.employee_benefits (employee_id)
fk_employees_employee_projects ( employee_id ) ref company.employee_projects (project_id)
fk_employees_salaries ( salary ) ref company.salaries (salary)
Options

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Table company.inventory

Idx Name Data Type
* 🔑 inventory_id INT
🔍 ⬈ product_id INT
warehouse_location VARCHAR(100)
stock_quantity INT
reorder_level INT
Indexes
Type Name On
🔑 pk_inventory ON inventory_id
🔍 unq_inventory_product_id ON product_id
Foreign Keys
Type Name On
Vir fk_inventory_products ( product_id ) ref company.products (product_id)
Options

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Table company.order_details

Idx Name Data Type
* 🔎 order_id INT
order_date DATE
🔎 ⬈ description INT
end_date DATE DEFAULT curdate()
Indexes
Type Name On
🔎 fk_order_details_shipping_details ON description
🔎 pk_order_details ON order_id
Foreign Keys
Type Name On
fk_order_details_shipping_details ( description ) ref company.shipping_details (shipping_id)
Options

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Table company.order_items

Idx Name Data Type
* 🔑 order_item_id INT
🔎 ⬈ order_id INT
product_id INT
quantity INT
price DECIMAL(10,2)
Indexes
Type Name On
🔑 pk_order_items ON order_item_id
🔎 fk_order_items_orders ON order_id
Foreign Keys
Type Name On
fk_order_items_orders ( order_id ) ref company.orders (order_id)
Options

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Table company.orders

Idx Name Data Type
* 🔑 ⬋ order_id INT
⬈ customer_id INT
order_date DATE
total_amount DECIMAL(15,2)
status VARCHAR(50)
shipping_address VARCHAR(255)
Indexes
Type Name On
🔑 pk_orders ON order_id
Foreign Keys
Type Name On
Vir fk_orders_customers ( customer_id ) ref company.customers (customer_id)
Options

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Table company.payment_methods

Idx Name Data Type
* 🔑 ⬋ payment_method_id INT
🔎 ⬈ customer_id INT
payment_type VARCHAR(50)
payment_details VARCHAR(255)
expiration_date DATE
Indexes
Type Name On
🔑 pk_payment_methods ON payment_method_id
🔎 fk_payment_methods_customers ON customer_id
Foreign Keys
Type Name On
fk_payment_methods_customers ( customer_id ) ref company.customers (customer_id)
Options

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Table company.product_price_log

Idx Name Data Type
* 🔑 log_id INT AUTO_INCREMENT
🔍 ⬈ product_id INT
old_price DECIMAL(10,2)
new_price DECIMAL(10,2)
change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
Indexes
Type Name On
🔑 pk_product_price_log ON log_id
🔍 unq_product_price_log_product_id ON product_id
Foreign Keys
Type Name On
Vir fk_product_price_log_inventory ( product_id ) ref company.inventory (product_id)
Options

ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Table company.products

Idx Name Data Type
* 🔑 ⬋ product_id INT
product_name VARCHAR(100)
price DECIMAL(10,2)
Indexes
Type Name On
🔑 pk_products ON product_id
Triggers
Name Definition

Trigger before_product_price_update

CREATE TRIGGER ${nameWithSchemaName} BEFORE UPDATE ON products FOR EACH ROW BEGIN
    IF OLD.price <> NEW.price THEN
        INSERT INTO company.product\_price\_log (product\_id, old\_price, new\_price)
        VALUES (OLD.product\_id, OLD.price, NEW.price);
    END IF;
END
Options

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Table company.projects

Idx Name Data Type
* 🔑 ⬈ project_id INT
project_name VARCHAR(100)
🔍 department_id INT
🔍 ⬋ start_date DATE
end_date DATE
budget DECIMAL(15,2)
status VARCHAR(50)
Indexes
Type Name On
🔑 pk_projects ON project_id
🔍 unq_projects_department_id ON department_id
🔍 unq_projects_start_date ON start_date
Foreign Keys
Type Name On
fk_projects_employee_projects ( project_id ) ref company.employee_projects (project_id)
Options

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Table company.reviews

Idx Name Data Type
* 🔑 review_id INT
🔍 product_id INT
🔎 ⬈ customer_id INT
rating INT
review_date DATE
comments TEXT
Indexes
Type Name On
🔑 pk_reviews ON review_id
🔍 unq_reviews_product_id ON product_id
🔎 fk_reviews_customers ON customer_id
Foreign Keys
Type Name On
fk_reviews_customers ( customer_id ) ref company.customers (customer_id)
Vir fk_reviews_sales ( customer_id ) ref company.sales (customer_id)
Options

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Table company.salaries

Idx Name Data Type
* 🔑 ⬈ salary_id INT
🔍 employee_id INT
🔍 ⬋ salary DECIMAL(10,2)
🔍 ⬋ effective_date DATE
bonus DECIMAL(10,2)
🔍 customer_id INT
Indexes
Type Name On
🔑 pk_salaries ON salary_id
🔍 unq_salaries_salary ON salary
🔍 unq_salaries_employee_id ON employee_id
🔍 unq_salaries_effective_date ON effective_date
🔍 unq_salaries_customer_id ON customer_id
Foreign Keys
Type Name On
fk_salaries_customers ( salary_id ) ref company.customers (customer_id)
fk_salaries_payment_methods ( salary_id ) ref company.payment_methods (payment_method_id)
Options

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Table company.sales

Idx Name Data Type
* 🔑 sales_id INT
price DECIMAL(10,0)
discount DECIMAL(10,0)
🔎 ⬈ customer_id INT
Indexes
Type Name On
🔑 pk_sales ON sales_id
🔎 fk_sales_customers ON customer_id
Foreign Keys
Type Name On
fk_sales_customers ( customer_id ) ref company.customers (customer_id)
Options

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Table company.shipping

Idx Name Data Type
* 🔑 ⬋ shipping_id INT
🔍 ⬋ order_id INT
shipping_date DATE
tracking_number VARCHAR(50)
shipping_method VARCHAR(50)
shipping_status VARCHAR(50)
Indexes
Type Name On
🔑 pk_shipping ON shipping_id
🔍 unq_shipping_order_id ON order_id
Options

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Table company.shipping_details

Idx Name Data Type
* 🔑 ⬈ shipping_id INT
description INT
* 🔎 ⬈ ship_date DATE DEFAULT curdate()
Indexes
Type Name On
🔑 pk_shipping_details ON shipping_id
🔎 idx_shipping_details_ship_date ON ship_date
Foreign Keys
Type Name On
fk_shipping_details_projects ( ship_date ) ref company.projects (start_date)
fk_shipping_details_shipping ( shipping_id ) ref company.shipping (shipping_id)
Options

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Table company.suppliers

Idx Name Data Type
* 🔑 ⬈ supplier_id INT
supplier_name VARCHAR(100)
contact_name VARCHAR(50)
contact_email VARCHAR(100)
phone_number VARCHAR(20)
address VARCHAR(255)
Indexes
Type Name On
🔑 pk_suppliers ON supplier_id
Foreign Keys
Type Name On
fk_suppliers_shipping ( supplier_id ) ref company.shipping (order_id)
Options

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Table company.transactions

Idx Name Data Type
* 🔑 transaction_id INT
🔎 ⬈ order_id INT
transaction_date DATE
amount DECIMAL(15,2)
payment_method_id INT
Indexes
Type Name On
🔑 pk_transactions ON transaction_id
🔎 fk_transactions_orders ON order_id
Foreign Keys
Type Name On
fk_transactions_orders ( order_id ) ref company.orders (order_id)
Options

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci