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 definitionsusers - System user accountscategories - Product categorizationproducts - Inventory itemsclients - Customer informationsales - Sales transactionssale_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.
categories Organizes products into categories.
CREATE TABLE categories ( id INT AUTO_INCREMENT PRIMARY KEY , name VARCHAR ( 100 ) NOT NULL UNIQUE , created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
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
Example categories: bebidas, alfajores, galletasproducts 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_detailsTables 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:
After selling 10 units:
Soft Deletes The products table uses a soft delete pattern:
is_deleted field marks products as deleted without removing themPreserves 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.nameusers.emailcategories.nameclients.email
Foreign Key Indexes (Automatically Created)
users.role_idproducts.category_idsales.clients_idsale_details.sale_idsale_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