1. Departments
CREATE TABLE departments ( department_id INT PRIMARY KEY, department_name VARCHAR(100), location VARCHAR(100) );
2. Employees
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(150),
department_id INT,
manager_id INT,
job_title VARCHAR(100),
salary DECIMAL(12, 2),
join_date DATE,
email VARCHAR(255),
CONSTRAINT fk_employee_department
FOREIGN KEY (department_id)
REFERENCES departments(department_id),
CONSTRAINT fk_employee_manager
FOREIGN KEY (manager_id)
REFERENCES employees(id)
);
manager_id is a self-referencing foreign key, because an employee can report to another employee.
3. Customers
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(150),
email VARCHAR(255),
phone VARCHAR(30),
signup_date DATE,
country VARCHAR(100)
);
4. Orders
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
amount DECIMAL(12, 2),
CONSTRAINT fk_order_customer
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
);
5. Order Items
CREATE TABLE order_items (
order_id INT,
product_id INT,
quantity INT,
price DECIMAL(12, 2),
PRIMARY KEY (order_id, product_id),
CONSTRAINT fk_order_item_order
FOREIGN KEY (order_id)
REFERENCES orders(order_id),
CONSTRAINT fk_order_item_product
FOREIGN KEY (product_id)
REFERENCES products(product_id)
);
6. Categories
CREATE TABLE categories (
category_id INT PRIMARY KEY,
category_name VARCHAR(100)
);
7. Products
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(200),
category_id INT,
price DECIMAL(12, 2),
vendor_id INT,
stock_quantity INT,
CONSTRAINT fk_product_category
FOREIGN KEY (category_id)
REFERENCES categories(category_id)
);