[DM-03] Voters table (CURP-based identification)

{
  "cost": {
    "input_tokens": 24,
    "estimated_usd": 0.032937,
    "output_tokens": 2191
  },
  "model": "claude-sonnet-4-20250514",
  "status": "success",
  "transport": "anthropic_api",
  "latency_ms": 30273,
  "response_text": "I'll help you design a voters table using CURP (Clave Única de Registro de Población) as the primary identification method for Mexican citizens.\n\n## Voters Table Design\n\n```sql\nCREATE TABLE voters (\n    voter_id BIGINT PRIMARY KEY AUTO_INCREMENT,\n    curp CHAR(18) UNIQUE NOT NULL,\n    ine_key VARCHAR(20) UNIQUE,\n    \n    -- Personal Information\n    first_name VARCHAR(50) NOT NULL,\n    paternal_surname VARCHAR(50) NOT NULL,\n    maternal_surname VARCHAR(50),\n    birth_date DATE NOT NULL,\n    gender ENUM('M', 'F') NOT NULL,\n    birth_state_code CHAR(2) NOT NULL,\n    birth_country_code CHAR(2) DEFAULT 'MX',\n    \n    -- Address Information\n    current_state_code CHAR(2) NOT NULL,\n    current_municipality_code CHAR(3) NOT NULL,\n    electoral_district_local SMALLINT,\n    electoral_district_federal SMALLINT,\n    address_line1 VARCHAR(100),\n    address_line2 VARCHAR(100),\n    neighborhood VARCHAR(50),\n    postal_code CHAR(5),\n    \n    -- Contact Information\n    phone_number VARCHAR(15),\n    email VARCHAR(100),\n    \n    -- Electoral Status\n    registration_status ENUM('ACTIVE', 'INACTIVE', 'SUSPENDED', 'DECEASED') DEFAULT 'ACTIVE',\n    registration_date DATE NOT NULL,\n    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n    \n    -- Validation\n    curp_validated BOOLEAN DEFAULT FALSE,\n    ine_validated BOOLEAN DEFAULT FALSE,\n    address_validated BOOLEAN DEFAULT FALSE,\n    \n    -- Metadata\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    created_by VARCHAR(50),\n    notes TEXT,\n    \n    -- Indexes\n    INDEX idx_curp (curp),\n    INDEX idx_ine_key (ine_key),\n    INDEX idx_name (paternal_surname, first_name),\n    INDEX idx_location (current_state_code, current_municipality_code),\n    INDEX idx_electoral_district (electoral_district_federal, electoral_district_local),\n    INDEX idx_registration_status (registration_status),\n    INDEX idx_birth_date (birth_date)\n);\n```\n\n## CURP Validation Function\n\n```sql\nDELIMITER //\nCREATE FUNCTION validate_curp(curp_input CHAR(18)) \nRETURNS BOOLEAN\nREADS SQL DATA\nDETERMINISTIC\nBEGIN\n    DECLARE is_valid BOOLEAN DEFAULT FALSE;\n    DECLARE calculated_checksum CHAR(1);\n    \n    -- Basic format validation\n    IF LENGTH(curp_input) = 18 AND \n       curp_input REGEXP '^[A-Z]{4}[0-9]{6}[HM][A-Z]{2}[A-Z0-9]{3}[0-9]$' THEN\n        \n        -- Here you would implement the full CURP validation algorithm\n        -- This is a simplified version\n        SET is_valid = TRUE;\n        \n        -- Validate birth date\n        IF STR_TO_DATE(SUBSTRING(curp_input, 5, 6), '%y%m%d') IS NULL THEN\n            SET is_valid = FALSE;\n        END IF;\n        \n        -- Validate gender\n        IF SUBSTRING(curp_input, 11, 1) NOT IN ('H', 'M') THEN\n            SET is_valid = FALSE;\n        END IF;\n        \n        -- Validate state codes (simplified check)\n        IF SUBSTRING(curp_input, 12, 2) NOT IN ('AS', 'BC', 'BS', 'CC', 'CL', 'CM', 'CS', 'CH', 'DF', 'DG', 'GT', 'GR', 'HG', 'JC', 'MC', 'MN', 'MS', 'NT', 'NL', 'OC', 'PL', 'QT', 'QR', 'SP', 'SL', 'SR', 'TC', 'TS', 'TL', 'VZ', 'YN', 'ZS', 'NE') THEN\n            SET is_valid = FALSE;\n        END IF;\n    END IF;\n    \n    RETURN is_valid;\nEND //\nDELIMITER ;\n```\n\n## Related Tables\n\n### Electoral Districts Table\n```sql\nCREATE TABLE electoral_districts (\n    district_id INT PRIMARY KEY,\n    district_type ENUM('FEDERAL', 'LOCAL') NOT NULL,\n    district_number SMALLINT NOT NULL,\n    state_code CHAR(2) NOT NULL,\n    municipality_codes JSON, -- Array of municipality codes\n    district_name VARCHAR(100),\n    active BOOLEAN DEFAULT TRUE,\n    \n    UNIQUE KEY unique_district (district_type, district_number, state_code)\n);\n```\n\n### States and Municipalities Reference\n```sql\nCREATE TABLE states (\n    state_code CHAR(2) PRIMARY KEY,\n    state_name VARCHAR(50) NOT NULL,\n    state_abbreviation CHAR(3),\n    active BOOLEAN DEFAULT TRUE\n);\n\nCREATE TABLE municipalities (\n    municipality_code CHAR(3) NOT NULL,\n    state_code CHAR(2) NOT NULL,\n    municipality_name VARCHAR(100) NOT NULL,\n    active BOOLEAN DEFAULT TRUE,\n    \n    PRIMARY KEY (municipality_code, state_code),\n    FOREIGN KEY (state_code) REFERENCES states(state_code)\n);\n```\n\n## Sample Data and Usage\n\n```sql\n-- Insert sample voter data\nINSERT INTO voters (\n    curp, ine_key, first_name, paternal_surname, maternal_surname,\n    birth_date, gender, birth_state_code, current_state_code,\n    current_municipality_code, registration_date, curp_validated\n) VALUES (\n    'GOPE900101HDFLBR05', 'GOPE90010112345678', 'Pedro', 'González', 'Pérez',\n    '1990-01-01', 'M', 'DF', 'DF', '015', '2008-07-01', TRUE\n);\n\n-- Query examples\n-- Find voter by CURP\nSELECT * FROM voters WHERE curp = 'GOPE900101HDFLBR05';\n\n-- Find voters by municipality\nSELECT v.*, m.municipality_name \nFROM voters v\nJOIN municipalities m ON v.current_municipality_code = m.municipality_code \n    AND v.current_state_code = m.state_code\nWHERE m.municipality_name = 'Guadalajara';\n\n-- Count voters by electoral district\nSELECT \n    electoral_district_federal,\n    COUNT(*) as voter_count\nFROM voters \nWHERE registration_status = 'ACTIVE'\nGROUP BY electoral_district_federal\nORDER BY electoral_district_federal;\n```\n\n## Triggers for Data Integrity\n\n```sql\n-- Trigger to validate CURP before insert/update\nDELIMITER //\nCREATE TRIGGER validate_voter_curp\nBEFORE INSERT ON voters\nFOR EACH ROW\nBEGIN\n    IF NOT validate_curp(NEW.curp) THEN\n        SIGNAL SQLSTATE '45000' \n        SET MESSAGE_TEXT = 'Invalid CURP format or content';\n    END IF;\n    \n    -- Extract and validate birth date from CURP matches the provided birth_date\n    IF NEW.birth_date != STR_TO_DATE(CONCAT('19', SUBSTRING(NEW.curp, 5, 6)), '%Y%m%d') \n       AND NEW.birth_date != STR_TO_DATE(CONCAT('20', SUBSTRING(NEW.curp, 5, 6)), '%Y%m%d') THEN\n        SIGNAL SQLSTATE '45000' \n        SET MESSAGE_TEXT = 'Birth date does not match CURP';\n    END IF;\nEND //\n\nCREATE TRIGGER validate_voter_curp_update\nBEFORE UPDATE ON voters\nFOR EACH ROW\nBEGIN\n    IF NEW.curp != OLD.curp AND NOT validate_curp(NEW.curp) THEN\n        SIGNAL SQLSTATE '45000' \n        SET MESSAGE_TEXT = 'Invalid CURP format or content';\n    END IF;\nEND //\nDELIMITER ;\n```\n\n## Key Features\n\n1. **CURP as Primary Identifier**: Unique 18-character Mexican citizen identifier\n2. **INE Integration**: Support for INE (voter ID) key correlation\n3. **Electoral District Mapping**: Both federal and local district assignments\n4. **Address Validation**: State and municipality code validation\n5. **Status Management**: Active, inactive, suspended, deceased status tracking\n6. **Data Validation**: Built-in CURP format and content validation\n7. **Audit Trail**: Creation and modification timestamps\n8. **Flexible Indexing**: Optimized for common query patterns\n\nThis design ensures data integrity while supporting the complex requirements of Mexican electoral systems using CURP as the primary identification method."
}