Database Schema

Schema Overview

The Muebles Roble database schema is designed to support a furniture management system with catalogs for colors, wood types, roles, units of measure, and furniture types.

Current Tables

colors

5 records - Color catalog for furniture

roles

User roles for access control

wood_types

Wood material classifications

unit_of_measures

Measurement units (cm, m, kg, etc.)

furniture_type

Furniture category classifications

Table Schemas

colors

Stores color catalog entries for furniture references.

Column	Type	Constraints	Description
`id_color`	INTEGER	PRIMARY KEY	Unique identifier
`name`	VARCHAR(50)	NOT NULL, UNIQUE	Color name
`active`	BOOLEAN	NOT NULL, DEFAULT TRUE	Active status
`created_at`	TIMESTAMP	NOT NULL, DEFAULT CURRENT_TIMESTAMP	Creation timestamp
`updated_at`	TIMESTAMP	NOT NULL, DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP	Last update timestamp
`deleted_at`	TIMESTAMP	NULL	Soft delete timestamp
`created_by`	VARCHAR(100)	NULL	User who created record
`updated_by`	VARCHAR(100)	NULL	User who updated record
`deleted_by`	VARCHAR(100)	NULL	User who deleted record

Indexes:

PRIMARY KEY on id_color
UNIQUE INDEX on name

roles

Manages user roles for permissions and access control.

Column	Type	Constraints	Description
`id_role`	INTEGER	PRIMARY KEY	Unique identifier
`name`	VARCHAR(50)	NOT NULL, UNIQUE	Role name (e.g., ‘Admin’, ‘Editor’)
`active`	BOOLEAN	NOT NULL, DEFAULT TRUE	Active status
`created_at`	TIMESTAMP	NOT NULL, DEFAULT CURRENT_TIMESTAMP	Creation timestamp
`updated_at`	TIMESTAMP	NOT NULL, DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP	Last update timestamp
`deleted_at`	TIMESTAMP	NULL	Soft delete timestamp
`created_by`	VARCHAR(100)	NULL	User who created record
`updated_by`	VARCHAR(100)	NULL	User who updated record
`deleted_by`	VARCHAR(100)	NULL	User who deleted record

Indexes:

PRIMARY KEY on id_role
UNIQUE INDEX on name

wood_types

Catalogs different types of wood materials.

Column	Type	Constraints	Description
`id_wood_type`	INTEGER	PRIMARY KEY	Unique identifier
`name`	VARCHAR(100)	NOT NULL, UNIQUE	Wood type name
`description`	VARCHAR(255)	NULL	Optional description
`active`	BOOLEAN	NOT NULL, DEFAULT TRUE	Active status
`created_at`	TIMESTAMP	NOT NULL, DEFAULT CURRENT_TIMESTAMP	Creation timestamp
`updated_at`	TIMESTAMP	NOT NULL, DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP	Last update timestamp
`deleted_at`	TIMESTAMP	NULL	Soft delete timestamp
`created_by`	VARCHAR(100)	NULL	User who created record
`updated_by`	VARCHAR(100)	NULL	User who updated record
`deleted_by`	VARCHAR(100)	NULL	User who deleted record

Indexes:

PRIMARY KEY on id_wood_type
UNIQUE INDEX on name

unit_of_measures

Manages measurement units for inventory and specifications.

Column	Type	Constraints	Description
`id_unit_of_measure`	INTEGER	PRIMARY KEY	Unique identifier
`name`	VARCHAR(50)	NOT NULL, UNIQUE	Unit name (e.g., “Centimeter”)
`abbreviation`	VARCHAR(10)	NOT NULL, UNIQUE	Unit abbreviation (e.g., “cm”)
`active`	BOOLEAN	NOT NULL, DEFAULT TRUE	Active status
`created_at`	TIMESTAMP	NOT NULL, DEFAULT CURRENT_TIMESTAMP	Creation timestamp
`updated_at`	TIMESTAMP	NOT NULL, DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP	Last update timestamp
`deleted_at`	TIMESTAMP	NULL	Soft delete timestamp
`created_by`	VARCHAR(100)	NULL	User who created record
`updated_by`	VARCHAR(100)	NULL	User who updated record
`deleted_by`	VARCHAR(100)	NULL	User who deleted record

Indexes:

PRIMARY KEY on id_unit_of_measure
UNIQUE INDEX on name
UNIQUE INDEX on abbreviation

furniture_type

Classifies different types of furniture.

Column	Type	Constraints	Description
`id_furniture_type`	INTEGER	PRIMARY KEY	Unique identifier
`name`	VARCHAR(50)	NOT NULL, UNIQUE	Furniture type name
`active`	BOOLEAN	NOT NULL, DEFAULT TRUE	Active status
`created_at`	TIMESTAMP	NOT NULL, DEFAULT CURRENT_TIMESTAMP	Creation timestamp
`updated_at`	TIMESTAMP	NOT NULL, DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP	Last update timestamp
`deleted_at`	TIMESTAMP	NULL	Soft delete timestamp
`created_by`	VARCHAR(100)	NULL	User who created record
`updated_by`	VARCHAR(100)	NULL	User who updated record
`deleted_by`	VARCHAR(100)	NULL	User who deleted record

Indexes:

PRIMARY KEY on id_furniture_type
UNIQUE INDEX on name

Common Schema Patterns

Standard Field Structure

All tables follow a consistent field pattern:

Primary Key
Audit Timestamps
User Tracking
Soft Delete

id_{table_name} INTEGER PRIMARY KEY AUTO_INCREMENT

Each table uses an integer primary key with a descriptive name.

created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 
           ON UPDATE CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL

Automatic timestamp tracking for all record changes.

created_by VARCHAR(100) NULL,
updated_by VARCHAR(100) NULL,
deleted_by VARCHAR(100) NULL

Tracks which user performed each action.

active BOOLEAN NOT NULL DEFAULT TRUE,
deleted_at TIMESTAMP NULL

Enables soft deletion of records.

Naming Conventions

Table Names

Use plural form: colors, roles, wood_types
Use snake_case: unit_of_measures, not UnitOfMeasures
Be descriptive and specific

Column Names

Use snake_case: id_color, created_at
Primary keys: id_{table_name} (e.g., id_color)
Foreign keys: id_{referenced_table} (e.g., id_color in furniture table)
Booleans: Use affirmative names (active, not inactive)

Constraint Names

Primary keys: pk_{table_name}
Foreign keys: fk_{table}_{referenced_table}
Unique constraints: uq_{table}_{column}
Indexes: idx_{table}_{column}

Data Types

String Fields

-- Short names and identifiers
name VARCHAR(50) NOT NULL

-- Longer text fields
description VARCHAR(255) NULL

-- Abbreviations
abbreviation VARCHAR(10) NOT NULL

-- User identifiers
created_by VARCHAR(100) NULL

-- Very long text
notes TEXT NULL

Numeric Fields

-- Integer IDs and counts
id_color INTEGER PRIMARY KEY
quantity INTEGER NOT NULL DEFAULT 0

-- Decimal values (prices, measurements)
price DECIMAL(10, 2) NOT NULL
width DECIMAL(8, 2) NULL

Date/Time Fields

-- Automatic timestamps
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 
           ON UPDATE CURRENT_TIMESTAMP

-- Manual dates
delivery_date DATE NULL
expiration_date DATE NULL

Boolean Fields

-- Status flags
active BOOLEAN NOT NULL DEFAULT TRUE
is_published BOOLEAN NOT NULL DEFAULT FALSE

Schema Evolution

Adding New Tables

When adding a new table, follow this template:

CREATE TABLE new_table (
    id_new_table INTEGER PRIMARY KEY AUTO_INCREMENT,
    
    -- Business fields
    name VARCHAR(100) NOT NULL,
    description VARCHAR(255) NULL,
    
    -- Status
    active BOOLEAN NOT NULL DEFAULT TRUE,
    
    -- Audit timestamps
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 
               ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    
    -- User tracking
    created_by VARCHAR(100) NULL,
    updated_by VARCHAR(100) NULL,
    deleted_by VARCHAR(100) NULL,
    
    -- Constraints
    UNIQUE KEY uq_new_table_name (name)
);

Adding Relationships

When adding foreign keys:

-- Add foreign key column
ALTER TABLE furniture 
ADD COLUMN id_color INTEGER NULL;

-- Add constraint
ALTER TABLE furniture
ADD CONSTRAINT fk_furniture_color 
FOREIGN KEY (id_color) REFERENCES colors(id_color)
ON DELETE SET NULL
ON UPDATE CASCADE;

-- Add index for performance
CREATE INDEX idx_furniture_color ON furniture(id_color);

Database Configuration

Connection Settings

Database configuration is managed through environment variables:

config.py

import os

class Config:
    DB_USER = os.getenv('DB_USER')
    DB_PASSWORD = os.getenv('DB_PASSWORD')
    DB_HOST = os.getenv('DB_HOST', 'localhost')
    DB_PORT = os.getenv('DB_PORT', '3306')
    DB_NAME = os.getenv('DB_NAME')
    
    SQLALCHEMY_DATABASE_URI = (
        f"mysql+pymysql://{DB_USER}:{DB_PASSWORD}@"
        f"{DB_HOST}:{DB_PORT}/{DB_NAME}"
    )
    SQLALCHEMY_TRACK_MODIFICATIONS = False

Environment Variables

.env

DB_USER=furniture_user
DB_PASSWORD=secure_password
DB_HOST=localhost
DB_PORT=3306
DB_NAME=furniture_store

Never commit .env files to version control. Use .env.example as a template.

Query Performance

Recommended Indexes

All tables have these indexes by default:

-- Primary key (automatic)
PRIMARY KEY (id_{table_name})

-- Unique constraint on name
UNIQUE KEY uq_{table}_name (name)

-- Performance indexes
INDEX idx_{table}_active (active)
INDEX idx_{table}_created_at (created_at)

Query Optimization

Filter Active Records
Case-Insensitive Search
Count Efficiently

Always filter by active status:

# Good - uses index
colors = Color.query.filter_by(active=True).all()

# Avoid - scans all records
colors = [c for c in Color.query.all() if c.active]

Use database functions for case-insensitive queries:

from sqlalchemy import func

# Case-insensitive search
color = Color.query.filter(
    func.lower(Color.name) == name.lower()
).first()

Use COUNT queries instead of loading all records:

# Good - database COUNT
count = Color.query.filter_by(active=True).count()

# Avoid - loads all records
count = len(Color.query.filter_by(active=True).all())

Schema Maintenance

Regular Tasks

Backup Database

mysqldump -u user -p furniture_store > backup_$(date +%Y%m%d).sql

Optimize Tables

OPTIMIZE TABLE colors, roles, wood_types, unit_of_measures, furniture_type;

Analyze Performance

ANALYZE TABLE colors;
SHOW INDEX FROM colors;

Monitor Growth

SELECT 
    table_name,
    table_rows,
    ROUND(data_length / 1024 / 1024, 2) as data_mb,
    ROUND(index_length / 1024 / 1024, 2) as index_mb
FROM information_schema.tables
WHERE table_schema = 'furniture_store';

System Design

Database

Error Handling

Schema Overview

Current Tables

colors

roles

wood_types

unit_of_measures

furniture_type

Table Schemas

colors

roles

wood_types

unit_of_measures

furniture_type

Common Schema Patterns

Standard Field Structure

Naming Conventions

Data Types

String Fields

Numeric Fields

Date/Time Fields

Boolean Fields

Schema Evolution

Adding New Tables

Adding Relationships

Database Configuration

Connection Settings

Environment Variables

Query Performance

Recommended Indexes

Query Optimization

Schema Maintenance

Regular Tasks

Next Steps

Database Models

Migrations

Build docs developers (and LLMs) love

System Design

Database

Error Handling

​Schema Overview

​Current Tables

colors

roles

wood_types

unit_of_measures

furniture_type

​Table Schemas

​colors

​roles

​wood_types

​unit_of_measures

​furniture_type

​Common Schema Patterns

​Standard Field Structure

​Naming Conventions

​Data Types

​String Fields

​Numeric Fields

​Date/Time Fields

​Boolean Fields

​Schema Evolution

​Adding New Tables

​Adding Relationships

​Database Configuration

​Connection Settings

​Environment Variables

​Query Performance

​Recommended Indexes

​Query Optimization

​Schema Maintenance

​Regular Tasks

​Next Steps

Database Models

Migrations

Build docs developers (and LLMs) love

Schema Overview

Current Tables

Table Schemas

colors

roles

wood_types

unit_of_measures

furniture_type

Common Schema Patterns

Standard Field Structure

Naming Conventions

Data Types

String Fields

Numeric Fields

Date/Time Fields

Boolean Fields

Schema Evolution

Adding New Tables

Adding Relationships

Database Configuration

Connection Settings

Environment Variables

Query Performance

Recommended Indexes

Query Optimization

Schema Maintenance

Regular Tasks

Next Steps