Main Diagram

Generated using DbSchema

Main Diagram

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 &lt;&gt; 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