Database Schema

The Sales Management System uses MySQL with a relational schema designed around sales, inventory, and client management.

Overview

The database consists of 7 main tables:

roles - User role definitions
users - System user accounts
categories - Product categorization
products - Inventory items
clients - Customer information
sales - Sales transactions
sale_details - Line items for each sale

Schema Diagram

Table Definitions

roles

Defines user roles for access control.

CREATE TABLE roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);

INSERT INTO roles (name) VALUES ('admin'), ('employee');

Column	Type	Constraints	Description
id	INT	PRIMARY KEY, AUTO_INCREMENT	Unique role identifier
name	VARCHAR(50)	NOT NULL, UNIQUE	Role name (admin, employee)

users

Stores system user accounts with role-based access.

CREATE TABLE users (
    id BINARY(16) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (role_id) REFERENCES roles(id)
);

Column	Type	Constraints	Description
id	BINARY(16)	PRIMARY KEY	UUID stored as binary
name	VARCHAR(100)	NOT NULL	User’s full name
email	VARCHAR(150)	NOT NULL, UNIQUE	User email address
password	VARCHAR(255)	NOT NULL	Hashed password
role_id	INT	NOT NULL, FOREIGN KEY	Reference to roles.id
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Account creation time
updated_at	TIMESTAMP	AUTO UPDATE	Last modification time

User passwords should be hashed using bcrypt or similar before storage.

Column	Type	Constraints	Description
id	INT	PRIMARY KEY, AUTO_INCREMENT	Unique category identifier
name	VARCHAR(100)	NOT NULL, UNIQUE	Category name (min 3 chars)
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Creation timestamp

products

Stores inventory items with pricing and stock information.

CREATE TABLE products (
    id BINARY(16) PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    category_id INT,
    is_deleted BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

Column	Type	Constraints	Description
id	BINARY(16)	PRIMARY KEY	UUID for product
name	VARCHAR(150)	NOT NULL	Product name (min 3 chars)
description	TEXT	-	Detailed description (min 10 chars)
price	DECIMAL(10,2)	NOT NULL	Price with 2 decimal places
stock	INT	NOT NULL, DEFAULT 0	Available quantity
category_id	INT	FOREIGN KEY	Reference to categories.id
is_deleted	BOOLEAN	DEFAULT FALSE	Soft delete flag
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Creation time
updated_at	TIMESTAMP	AUTO UPDATE	Last update time

Example:

{
  "id": "b6da7bbc-14b9-11f1-9fcd-2418c6c96a00",
  "name": "Coca cola",
  "description": "Bebida gasificada de 2ltr",
  "price": "3000.00",
  "stock": 40,
  "category_id": 1
}

clients

Stores customer information.

CREATE TABLE clients(
    id BINARY(16) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE
);

Column	Type	Constraints	Description
id	BINARY(16)	PRIMARY KEY	UUID for client
name	VARCHAR(100)	NOT NULL	Client’s full name
email	VARCHAR(150)	NOT NULL, UNIQUE	Client email address

Example:

{
  "id": "036c71c5-14e0-11f1-9fcd-2418c6c96a00",
  "name": "Mark Zuckemberg",
  "email": "markz@facebook.com"
}

sales

Records sales transactions.

CREATE TABLE sales (
    id BINARY(16) PRIMARY KEY,
    clients_id BINARY(16) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (clients_id) REFERENCES clients(id)
);

Column	Type	Constraints	Description
id	BINARY(16)	PRIMARY KEY	UUID for sale
clients_id	BINARY(16)	NOT NULL, FOREIGN KEY	Reference to clients.id
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Sale timestamp

A sale cannot be deleted if the client has associated sales records. Handle client deletion carefully.

sale_details

Stores line items for each sale transaction.

CREATE TABLE sale_details (
    id BINARY(16) PRIMARY KEY,
    sale_id BINARY(16) NOT NULL,
    product_id BINARY(16) NOT NULL,
    quantity INT NOT NULL,
    FOREIGN KEY (sale_id) REFERENCES sales(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

Column	Type	Constraints	Description
id	BINARY(16)	PRIMARY KEY	UUID for sale detail
sale_id	BINARY(16)	NOT NULL, FOREIGN KEY	Reference to sales.id
product_id	BINARY(16)	NOT NULL, FOREIGN KEY	Reference to products.id
quantity	INT	NOT NULL	Quantity sold

ON DELETE CASCADE ensures that when a sale is deleted, all its line items are automatically removed.

Relationships

One-to-Many Relationships

roles → users: One role has many users
- Enforced by: users.role_id → roles.id
categories → products: One category has many products
- Enforced by: products.category_id → categories.id
- Constraint: Cannot delete category if products exist with that category
clients → sales: One client has many sales
- Enforced by: sales.clients_id → clients.id
- Constraint: Cannot delete client if they have sales
sales → sale_details: One sale has many line items
- Enforced by: sale_details.sale_id → sales.id
- Cascade: Deleting a sale deletes all its details
products → sale_details: One product appears in many sales
- Enforced by: sale_details.product_id → products.id
- Constraint: Cannot delete product if it appears in sales

UUID Usage

The system uses BINARY(16) to store UUIDs efficiently:

Tables using UUIDs: users, clients, products, sales, sale_details
Tables using integers: roles, categories

Working with UUIDs

MySQL provides functions to convert between UUID strings and binary:

-- Convert UUID string to binary for INSERT
INSERT INTO clients (id, name, email) 
VALUES (UUID_TO_BIN('550e8400-e29b-41d4-a716-446655440000'), 'John', 'john@example.com');

-- Convert binary to UUID string for SELECT
SELECT BIN_TO_UUID(id) as id, name, email FROM clients;

The API handles UUID conversion automatically in model files (e.g., src/model/categorie.model.js:33).

Stock Management

When a sale is created:

Product stock is validated (must have sufficient quantity)
Stock is decremented by the quantity sold
Transaction is recorded in sale_details

Example: Before sale:

{"stock": 40}

After selling 10 units:

{"stock": 30}

Soft Deletes

The products table uses a soft delete pattern:

is_deleted field marks products as deleted without removing them
Preserves referential integrity with existing sales
Products can be filtered in queries: WHERE is_deleted = FALSE

Indexes

Primary Keys (Automatically Indexed)

All id columns are indexed as primary keys

Unique Indexes

roles.name
users.email
categories.name
clients.email

Foreign Key Indexes (Automatically Created)

users.role_id
products.category_id
sales.clients_id
sale_details.sale_id
sale_details.product_id

Database Size Considerations

Table	Estimated Size per Row
roles	~60 bytes
users	~500 bytes
categories	~120 bytes
products	~300 bytes
clients	~270 bytes
sales	~50 bytes
sale_details	~50 bytes

Actual size varies based on data length. TEXT fields in products can significantly increase row size.

Next Steps

Architecture

Learn how the API interacts with the database

API Reference

See how to query and modify data

Error Handling

Handle foreign key constraint errors

Configuration

Configure database connection

Get Started

Core Concepts

Guides

Database Schema

Database Schema

Overview

Schema Diagram

Table Definitions

roles

users

categories

products

clients

sales

sale_details

Relationships

One-to-Many Relationships

UUID Usage

Working with UUIDs

Stock Management

Soft Deletes

Indexes

Primary Keys (Automatically Indexed)

Unique Indexes

Foreign Key Indexes (Automatically Created)

Database Size Considerations

Next Steps

Architecture

API Reference

Error Handling

Configuration

Build docs developers (and LLMs) love

Get Started

Core Concepts

Guides

Documentation Index

​Database Schema

​Overview

​Schema Diagram

​Table Definitions

​roles

​users

​categories

​products

​clients

​sales

​sale_details

​Relationships

​One-to-Many Relationships

​UUID Usage

​Working with UUIDs

​Stock Management

​Soft Deletes

​Indexes

​Primary Keys (Automatically Indexed)

​Unique Indexes

​Foreign Key Indexes (Automatically Created)

​Database Size Considerations

​Next Steps

Architecture

API Reference

Error Handling

Configuration

Build docs developers (and LLMs) love

Database Schema

Overview

Schema Diagram

Table Definitions

roles

users

categories

products

clients

sales

sale_details

Relationships

One-to-Many Relationships

UUID Usage

Working with UUIDs

Stock Management

Soft Deletes

Indexes

Primary Keys (Automatically Indexed)

Unique Indexes

Foreign Key Indexes (Automatically Created)

Database Size Considerations

Next Steps