Military Personnel Management System

Database design is like architecture - you need a solid foundation before building anything. This military management system taught me how to design schemas that handle complex real-world relationships while maintaining data integrity.

System Requirements

Military operations require tracking: - Personnel: Soldiers, officers, ranks, assignments - Units: Battalions, companies, platoons with hierarchy - Equipment: Weapons, vehicles, assignments - Operations: Missions, deployments, tactical planning - Training: Courses, certifications, schedules

ER Diagram Design

The core challenge was representing military hierarchy:

-- Unit hierarchy with self-referencing foreign key
CREATE TABLE units (
    unit_id SERIAL PRIMARY KEY,
    unit_name VARCHAR(100),
    unit_type VARCHAR(50),
    parent_unit_id INTEGER REFERENCES units(unit_id),
    commander_id INTEGER REFERENCES personnel(personnel_id)
);

-- Personnel with rank and assignment
CREATE TABLE personnel (
    personnel_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    rank VARCHAR(50),
    specialty VARCHAR(100),
    assigned_unit_id INTEGER REFERENCES units(unit_id),
    enlistment_date DATE,
    status VARCHAR(20)
);

Complex Queries

Finding all subordinate units recursively:

WITH RECURSIVE unit_hierarchy AS (
    SELECT unit_id, unit_name, parent_unit_id, 0 as level
    FROM units
    WHERE unit_id = 1

    UNION ALL

    SELECT u.unit_id, u.unit_name, u.parent_unit_id, uh.level + 1
    FROM units u
    INNER JOIN unit_hierarchy uh ON u.parent_unit_id = uh.unit_id
)
SELECT * FROM unit_hierarchy;

This query finds all units under a commander - critical for deployment planning.