Query

billing.db — cable_maintenance_logs

SQL

CREATE TABLE cable_maintenance_logs  (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  cable_route_id INTEGER,
  network_segment_id INTEGER,
  maintenance_type VARCHAR(50) NOT NULL CHECK (maintenance_type IN ('repair', 'replacement', 'inspection', 'upgrade')),
  description TEXT NOT NULL,
  performed_by INTEGER,
  maintenance_date DATE NOT NULL,
  duration_hours DECIMAL(4,2),
  cost DECIMAL(12,2),
  notes TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (cable_route_id) REFERENCES cable_routes(id) ON DELETE CASCADE,
  FOREIGN KEY (network_segment_id) REFERENCES network_segments(id) ON DELETE CASCADE
)

+ Add column

Columns

Column Data type Allow null Primary key Actions
id INTEGER ✓ ✓ Rename | Drop
cable_route_id INTEGER ✓ Rename | Drop
network_segment_id INTEGER ✓ Rename | Drop
maintenance_type VARCHAR(50) Rename | Drop
description TEXT Rename | Drop
performed_by INTEGER ✓ Rename | Drop
maintenance_date DATE Rename | Drop
duration_hours DECIMAL(4,2) ✓ Rename | Drop
cost DECIMAL(12,2) ✓ Rename | Drop
notes TEXT ✓ Rename | Drop
created_at DATETIME ✓ Rename | Drop

Foreign Keys

Column Destination
network_segment_id network_segments.id
cable_route_id cable_routes.id

+ Add index

Indexes

Name Columns Unique SQL Drop?
idx_maintenance_logs_date maintenance_date SQL 
CREATE INDEX idx_maintenance_logs_date
ON cable_maintenance_logs(maintenance_date)
Drop
idx_maintenance_logs_route cable_route_id SQL 
CREATE INDEX idx_maintenance_logs_route
ON cable_maintenance_logs(cable_route_id)
Drop
idx_maintenance_logs_segment network_segment_id SQL 
CREATE INDEX idx_maintenance_logs_segment
ON cable_maintenance_logs(network_segment_id)
Drop