[DM-04] Territories table (geographic assignment)

{
  "cost": {
    "input_tokens": 20,
    "estimated_usd": 0.03741,
    "output_tokens": 2490
  },
  "model": "claude-sonnet-4-20250514",
  "status": "success",
  "transport": "anthropic_api",
  "latency_ms": 32787,
  "response_text": "I'll help you design a Territories table for geographic assignment. This table is commonly used in sales management, logistics, and regional administration systems.\n\n## Territories Table Structure\n\n```sql\n-- Territories table for geographic assignment\nCREATE TABLE territories (\n    territory_id VARCHAR(20) PRIMARY KEY,\n    territory_name VARCHAR(100) NOT NULL,\n    territory_type ENUM('country', 'state', 'region', 'district', 'city', 'postal_code') NOT NULL,\n    parent_territory_id VARCHAR(20),\n    territory_manager_id INT,\n    description TEXT,\n    \n    -- Geographic boundaries\n    coordinates JSON, -- For polygon/boundary definitions\n    postal_codes TEXT, -- Comma-separated list or JSON array\n    cities TEXT,\n    \n    -- Business attributes\n    sales_quota DECIMAL(15,2),\n    population_size INT,\n    market_potential ENUM('high', 'medium', 'low'),\n    territory_priority TINYINT DEFAULT 3, -- 1=high, 5=low\n    \n    -- Status and metadata\n    status ENUM('active', 'inactive', 'pending') DEFAULT 'active',\n    effective_date DATE NOT NULL,\n    end_date DATE,\n    \n    -- Audit fields\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n    created_by INT,\n    \n    -- Foreign key constraints\n    FOREIGN KEY (parent_territory_id) REFERENCES territories(territory_id),\n    FOREIGN KEY (territory_manager_id) REFERENCES employees(employee_id),\n    FOREIGN KEY (created_by) REFERENCES users(user_id),\n    \n    -- Indexes\n    INDEX idx_territory_type (territory_type),\n    INDEX idx_territory_manager (territory_manager_id),\n    INDEX idx_parent_territory (parent_territory_id),\n    INDEX idx_status_effective (status, effective_date),\n    INDEX idx_territory_name (territory_name)\n);\n```\n\n## Sample Data\n\n```sql\n-- Insert sample territory data\nINSERT INTO territories (\n    territory_id, territory_name, territory_type, parent_territory_id,\n    territory_manager_id, description, sales_quota, population_size,\n    market_potential, territory_priority, effective_date\n) VALUES\n-- Country level\n('US', 'United States', 'country', NULL, 1001, 'Primary market territory', 10000000.00, 331000000, 'high', 1, '2024-01-01'),\n\n-- Regional level\n('US-WEST', 'Western Region', 'region', 'US', 1002, 'Western United States including CA, OR, WA, NV', 3000000.00, 78000000, 'high', 1, '2024-01-01'),\n('US-EAST', 'Eastern Region', 'region', 'US', 1003, 'Eastern United States including NY, FL, MA, PA', 3500000.00, 112000000, 'high', 1, '2024-01-01'),\n('US-CENTRAL', 'Central Region', 'region', 'US', 1004, 'Central United States including TX, IL, OH, MI', 3500000.00, 141000000, 'high', 1, '2024-01-01'),\n\n-- State level\n('US-CA', 'California', 'state', 'US-WEST', 1005, 'California state territory', 1500000.00, 39500000, 'high', 1, '2024-01-01'),\n('US-TX', 'Texas', 'state', 'US-CENTRAL', 1006, 'Texas state territory', 1400000.00, 29000000, 'high', 1, '2024-01-01'),\n('US-NY', 'New York', 'state', 'US-EAST', 1007, 'New York state territory', 1200000.00, 19500000, 'high', 1, '2024-01-01'),\n\n-- District level\n('US-CA-SF', 'San Francisco District', 'district', 'US-CA', 1008, 'San Francisco Bay Area', 500000.00, 7700000, 'high', 1, '2024-01-01'),\n('US-CA-LA', 'Los Angeles District', 'district', 'US-CA', 1009, 'Greater Los Angeles Area', 700000.00, 13200000, 'high', 1, '2024-01-01'),\n('US-TX-DAL', 'Dallas District', 'district', 'US-TX', 1010, 'Dallas-Fort Worth Metroplex', 450000.00, 7600000, 'high', 1, '2024-01-01'),\n\n-- City level\n('US-CA-SF-SF', 'San Francisco City', 'city', 'US-CA-SF', 1011, 'San Francisco city limits', 200000.00, 875000, 'high', 1, '2024-01-01'),\n('US-CA-LA-LA', 'Los Angeles City', 'city', 'US-CA-LA', 1012, 'Los Angeles city limits', 300000.00, 4000000, 'high', 1, '2024-01-01');\n```\n\n## Related Tables\n\n```sql\n-- Territory assignments (linking customers/accounts to territories)\nCREATE TABLE territory_assignments (\n    assignment_id INT AUTO_INCREMENT PRIMARY KEY,\n    territory_id VARCHAR(20) NOT NULL,\n    entity_type ENUM('customer', 'account', 'lead', 'prospect') NOT NULL,\n    entity_id INT NOT NULL,\n    assignment_date DATE NOT NULL,\n    assignment_reason VARCHAR(255),\n    assigned_by INT,\n    \n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    \n    FOREIGN KEY (territory_id) REFERENCES territories(territory_id),\n    FOREIGN KEY (assigned_by) REFERENCES users(user_id),\n    \n    UNIQUE KEY unique_entity_assignment (entity_type, entity_id, territory_id),\n    INDEX idx_territory_entity (territory_id, entity_type),\n    INDEX idx_assignment_date (assignment_date)\n);\n\n-- Territory coverage (postal codes, geographic areas)\nCREATE TABLE territory_coverage (\n    coverage_id INT AUTO_INCREMENT PRIMARY KEY,\n    territory_id VARCHAR(20) NOT NULL,\n    coverage_type ENUM('postal_code', 'city', 'county', 'coordinates') NOT NULL,\n    coverage_value VARCHAR(100) NOT NULL,\n    country_code CHAR(2),\n    state_code VARCHAR(10),\n    \n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    \n    FOREIGN KEY (territory_id) REFERENCES territories(territory_id),\n    \n    INDEX idx_territory_coverage (territory_id, coverage_type),\n    INDEX idx_coverage_lookup (coverage_type, coverage_value),\n    INDEX idx_location (country_code, state_code)\n);\n```\n\n## Common Queries\n\n```sql\n-- 1. Get territory hierarchy\nSELECT \n    t1.territory_id,\n    t1.territory_name,\n    t1.territory_type,\n    t2.territory_name AS parent_territory,\n    t1.territory_manager_id,\n    t1.sales_quota\nFROM territories t1\nLEFT JOIN territories t2 ON t1.parent_territory_id = t2.territory_id\nWHERE t1.status = 'active'\nORDER BY t1.territory_type, t1.territory_name;\n\n-- 2. Get all sub-territories for a region\nWITH RECURSIVE territory_tree AS (\n    -- Base case: start with parent territory\n    SELECT territory_id, territory_name, territory_type, parent_territory_id, 0 as level\n    FROM territories \n    WHERE territory_id = 'US-WEST'\n    \n    UNION ALL\n    \n    -- Recursive case: find children\n    SELECT t.territory_id, t.territory_name, t.territory_type, t.parent_territory_id, tt.level + 1\n    FROM territories t\n    INNER JOIN territory_tree tt ON t.parent_territory_id = tt.territory_id\n    WHERE t.status = 'active'\n)\nSELECT * FROM territory_tree ORDER BY level, territory_name;\n\n-- 3. Territory performance summary\nSELECT \n    t.territory_id,\n    t.territory_name,\n    t.sales_quota,\n    COUNT(ta.entity_id) as assigned_accounts,\n    t.population_size,\n    t.market_potential\nFROM territories t\nLEFT JOIN territory_assignments ta ON t.territory_id = ta.territory_id \n    AND ta.entity_type = 'account'\nWHERE t.status = 'active'\nGROUP BY t.territory_id\nORDER BY t.sales_quota DESC;\n\n-- 4. Find territory by postal code\nSELECT DISTINCT t.*\nFROM territories t\nINNER JOIN territory_coverage tc ON t.territory_id = tc.territory_id\nWHERE tc.coverage_type = 'postal_code' \n    AND tc.coverage_value = '94102'\n    AND t.status = 'active';\n\n-- 5. Territory manager workload\nSELECT \n    e.employee_id,\n    e.first_name,\n    e.last_name,\n    COUNT(t.territory_id) as territories_managed,\n    SUM(t.sales_quota) as total_quota\nFROM employees e\nINNER JOIN territories t ON e.employee_id = t.territory_manager_id\nWHERE t.status = 'active'\nGROUP BY e.employee_id\nORDER BY territories_managed DESC;\n```\n\n## Key Features\n\n1. **Hierarchical Structure**: Supports multi-level territory organization\n2. **Geographic Coverage**: Links territories to postal codes, cities, coordinates\n3. **Business Metrics**: Sales quotas, market potential, priorities\n4. **Assignment Tracking**: Links customers/accounts to territories\n5. **Temporal Support**: Effective dates for territory changes\n6. **Flexible Geography**: Supports various geographic boundary definitions\n\nThis design supports complex territory management scenarios while maintaining flexibility for different geographic assignment needs."
}