Simple MySQL example for E-commice

Hi everyone, I wanted to share my experience with SQL. In this post, I’ll walk through some snippets that illustrate what a simple e-commerce schema might look like.

`-- Role table
CREATE TABLE role (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL UNIQUE,
description VARCHAR(255)
);

-- Insert default roles
INSERT INTO role (name, description) VALUES
('admin', 'Full system access'),
('customer', 'Can browse and place orders'),
('staff', 'Can manage orders and products'),
('vendor', 'Supplier with limited access');

-- User table
CREATE TABLE user (
id INT AUTO_INCREMENT PRIMARY KEY,
role_id INT NOT NULL,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
phone VARCHAR(20),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (role_id) REFERENCES role(id)
);

-- Producer table (linked to a user)
CREATE TABLE producer (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL UNIQUE,
bio TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE
);

-- Category table
CREATE TABLE category (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL UNIQUE
);

-- Insert default categories
INSERT INTO category (name) VALUES
('Vegetables'),
('Fruits'),
('Dairy'),
('Honey & Preserves'),
('Meat & Poultry'),
('Drinks'),
('Bakery'),
('Herbs & Flowers');

-- Product table
CREATE TABLE product (
id INT AUTO_INCREMENT PRIMARY KEY,
producer_id INT NOT NULL,
category_id INT NOT NULL,
name VARCHAR(255) NOT NULL,
description TEXT,
price DECIMAL(10, 2) NOT NULL,
stock_qty INT NOT NULL DEFAULT 0,
unit VARCHAR(50) NOT NULL DEFAULT 'item',
is_organic BOOLEAN DEFAULT FALSE,
is_available BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (producer_id) REFERENCES producer(id) ON DELETE CASCADE,
FOREIGN KEY (category_id) REFERENCES category(id)
);

-- Address table
CREATE TABLE address (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
street VARCHAR(255) NOT NULL,
city VARCHAR(100) NOT NULL,
postcode VARCHAR(20) NOT NULL,
country VARCHAR(100) NOT NULL DEFAULT 'United Kingdom',
is_default BOOLEAN DEFAULT FALSE,
FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE
);

-- Order table
CREATE TABLE order (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
address_id INT NOT NULL,
status ENUM('pending', 'confirmed', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
total_price DECIMAL(10, 2) NOT NULL,
payment_status ENUM('unpaid', 'paid', 'refunded') DEFAULT 'unpaid',
payment_method VARCHAR(50),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES user(id),
FOREIGN KEY (address_id) REFERENCES address(id)
);

-- Order item table
CREATE TABLE order_item (
id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL DEFAULT 1,
unit_price DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (order_id) REFERENCES order(id) ON DELETE CASCADE,
FOREIGN KEY (product_id) REFERENCES product(id)
);`

Any feedback or suggestions for improvement would be appreciated!