Skip to main content

​
Overview

The Veterinaria ALFA Inventory System uses SQLite as its embedded database. The schema consists of two main tables that manage product inventory and sales history.
The database is automatically created at ./db/baseDeDatosInventario.db on first application launch via the crearBaseDeDatos() method in InventarioDAO.java.

​
Tables

​
productos

Stores information about all pharmaceutical products in the veterinary inventory.
​
id
INTEGER
required
Primary key, auto-incremented unique identifier for each product.
​
nombre
TEXT
required
Name of the pharmaceutical product or medication.
​
existencias
INTEGER
required
Current stock quantity available in inventory. Must be non-negative.
​
lote
TEXT
required
Batch or lot number for product traceability.
​
caducidad
TEXT
required
Expiration date in yyyy-MM format (e.g., “2025-07”). Used for expiration alerts and visual warnings.
​
fechaEntrada
TEXT
required
Date the product was added to inventory in ISO format yyyy-MM-dd (e.g., “2024-03-15”).
​
fecha_separado
TEXT
Date the product was reserved (apartado) in ISO format yyyy-MM-dd. NULL if product is not reserved.

​
SQL Definition

CREATE TABLE IF NOT EXISTS productos (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    nombre TEXT,
    existencias INTEGER,
    lote TEXT,
    caducidad TEXT,
    fechaEntrada TEXT,
    fecha_separado TEXT
)

​
Table Indexes

  • Primary Key: Automatically indexed on id column
  • No additional indexes defined

​
Constraints

  • id: Auto-incrementing primary key
  • existencias: Application-level validation ensures non-negative values
  • caducidad: Application-level validation ensures yyyy-MM format
  • fecha_separado: Can be NULL for non-reserved products

​
historial_ventas

Records all sales transactions, tracking which products were sold, quantities, and dates.
​
id
INTEGER
required
Primary key, auto-incremented unique identifier for each sale record.
​
idProducto
INTEGER
required
Foreign key referencing productos.id. Links the sale to the specific product.
​
nombre
TEXT
required
Product name at time of sale (denormalized for historical record keeping).
​
cantidad
INTEGER
required
Quantity of product sold in this transaction. Must be positive.
​
fechaVenta
TEXT
required
Date of the sale in ISO format yyyy-MM-dd (e.g., “2024-03-15”).

​
SQL Definition

CREATE TABLE IF NOT EXISTS historial_ventas (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    idProducto INTEGER,
    nombre TEXT,
    cantidad INTEGER,
    fechaVenta TEXT,
    FOREIGN KEY (idProducto) REFERENCES productos(id) ON DELETE CASCADE
)

​
Foreign Keys

Relationship: Many-to-OneOn Delete: CASCADE - When a product is deleted from productos, all associated sales records are automatically deletedNote: This maintains referential integrity but means historical sales data is lost if a product is deleted. Consider soft deletes for production use.

​
Table Indexes

  • Primary Key: Automatically indexed on id column
  • Foreign Key: Automatically indexed on idProducto column (SQLite behavior)

​
Data Types

​
Date Storage

All dates are stored as TEXT in ISO-8601 format:
FieldFormatExampleUsage
fechaEntradayyyy-MM-dd2024-03-15Product entry date
fechaVentayyyy-MM-dd2024-03-15Sale transaction date
fecha_separadoyyyy-MM-dd2024-03-15Reservation date
caducidadyyyy-MM2025-07Expiration year-month
SQLite does not have a native date/time type. Dates are stored as TEXT and parsed using Java’s LocalDate and YearMonth classes.

​
Numeric Types

  • INTEGER: Used for IDs, quantities, and stock levels
  • TEXT: Used for all string data and dates

​
Relationships

​
Common Queries

​
Get All Products

SELECT * FROM productos
Location: InventarioDAO.obtenerProductos() (line 94)

​
Get Products Near Expiration

SELECT * FROM productos
Filtered in Java code to check expiration dates within threshold. Location: InventarioDAO.obtenerMedicamentosProximosACaducar(int diasUmbral) (line 486)

​
Get Expired Products

SELECT * FROM productos
Filtered in Java code to find products past expiration date. Location: InventarioDAO.obtenerMedicamentosCaducados() (line 521)

​
Get Reserved Products

SELECT * FROM productos 
WHERE fecha_separado IS NOT NULL AND fecha_separado <> ''
Location: InventarioDAO.obtenerProductosApartados() (line 571)

​
Get Sales History

SELECT id, idProducto, nombre, cantidad, fechaVenta 
FROM historial_ventas 
ORDER BY id ASC
Location: InventarioDAO.obtenerHistorialVentas() (line 426)

​
Add Product

INSERT INTO productos (nombre, existencias, lote, caducidad, fechaEntrada) 
VALUES (?, ?, ?, ?, ?)
Location: InventarioDAO.agregarProducto() (line 119)

​
Register Sale

-- Step 1: Verify product exists and has sufficient stock
SELECT nombre, existencias FROM productos WHERE id = ?

-- Step 2: Decrease inventory (within transaction)
UPDATE productos SET existencias = existencias - ? WHERE id = ?

-- Step 3: Record sale (within transaction)
INSERT INTO historial_ventas (idProducto, nombre, cantidad, fechaVenta) 
VALUES (?, ?, ?, ?)
Location: InventarioDAO.registrarVenta() (line 247)
Sales are processed within a transaction to ensure atomicity. If any step fails, all changes are rolled back.

​
Database Initialization

The database and tables are created automatically on first run: 
public void crearBaseDeDatos() {
    try (Statement stmt = connection.createStatement()) {
        // Create productos table
        stmt.execute("CREATE TABLE IF NOT EXISTS productos (...)");
        
        // Create historial_ventas table with foreign key
        stmt.execute("CREATE TABLE IF NOT EXISTS historial_ventas (...)");
    } catch (SQLException e) {
        e.printStackTrace();
    }
}
Location: InventarioDAO.crearBaseDeDatos() (line 69)

​
Data Validation

​
Application-Level Validation

The application enforces these business rules before database operations:
​
existencias
integer
required
Must be non-negative (≥ 0). Validated before insert/update operations.
​
caducidad
string
required
Must match format yyyy-MM and be a valid year-month. Validated using regex and YearMonth.parse().
​
nombre
string
required
Cannot be empty or whitespace-only. Trimmed before validation.
​
cantidad
integer
required
Must be positive (> 0) for sales. Must not exceed available stock.

​
Stock Validation

Before registering a sale, the system verifies:
  1. Product exists by ID
  2. Product name matches (for additional verification)
  3. Sufficient stock is available (cantidad <= existencias)

​
Database Location

The database is created in a db/ subdirectory relative to the application:
./db/baseDeDatosInventario.db
If running from JAR: ./db/ is relative to JAR locationIf running from IDE: ./db/ is relative to project root
To use a custom database location, provide a JDBC URL to the InventarioDAO constructor:
String customUrl = "jdbc:sqlite:/path/to/custom/database.db";
InventarioDAO dao = new InventarioDAO(customUrl);

​
Backup and Export

​
CSV Export

The application provides CSV export functionality for:
  1. Complete Inventory (exportarInventarioCSV)
  2. Sales History (exportarCSV)
  3. Reserved Products (exportarApartadosCSV)
Exports include all relevant fields in comma-separated format with headers.

​
Database Backup

To backup the database, simply copy the ./db/baseDeDatosInventario.db file while the application is closed.

Build docs developers (and LLMs) love

Get started for freeTalk to us