Database Schema

The Busoga One Radio Management System (BORMS) relies on a robust database structure to efficiently manage all aspects of radio operations. This page outlines the database schema, relationships between tables, and key fields that power the system's functionality.

Figure 1: BORMS Database Schema - Entity Relationship Diagram

Core Database Tables

The BORMS database is structured around the following core tables that store and manage data across the system:

CREATE TABLE clients (
  id INT PRIMARY KEY AUTO_INCREMENT,
  client_name VARCHAR(100) NOT NULL,
  contact_person VARCHAR(100),
  email VARCHAR(100) UNIQUE,
  phone VARCHAR(20),
  address TEXT,
  status ENUM('active', 'inactive', 'prospect') DEFAULT 'prospect',
  classification_id INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (classification_id) REFERENCES client_classifications(id)
);

CREATE TABLE programs (
  id INT PRIMARY KEY AUTO_INCREMENT,
  program_name VARCHAR(100) NOT NULL,
  description TEXT,
  program_type ENUM('talk', 'music', 'news', 'special', 'sponsored') NOT NULL,
  default_duration INT NOT NULL COMMENT 'Duration in minutes',
  active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE invoices (
  id INT PRIMARY KEY AUTO_INCREMENT,
  client_id INT NOT NULL,
  invoice_number VARCHAR(50) UNIQUE NOT NULL,
  issue_date DATE NOT NULL,
  due_date DATE NOT NULL,
  amount_total DECIMAL(10,2) NOT NULL,
  status ENUM('draft', 'issued', 'paid', 'partially_paid', 'overdue', 'canceled') DEFAULT 'draft',
  notes TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES clients(id)
);

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  full_name VARCHAR(100) NOT NULL,
  role_id INT NOT NULL,
  status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
  last_login TIMESTAMP NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (role_id) REFERENCES roles(id)
);

CREATE TABLE content_items (
  id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(100) NOT NULL,
  description TEXT,
  content_type ENUM('advertisement', 'psa', 'station_id', 'jingle', 'interview', 'news') NOT NULL,
  duration INT NOT NULL COMMENT 'Duration in seconds',
  file_path VARCHAR(255),
  client_id INT NULL,
  category_id INT NOT NULL,
  approval_status ENUM('pending', 'approved', 'rejected', 'expired') DEFAULT 'pending',
  created_by INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES clients(id),
  FOREIGN KEY (category_id) REFERENCES content_categories(id),
  FOREIGN KEY (created_by) REFERENCES users(id)
);

Database Relationships

The power of the BORMS database lies in the relationships between tables that create a complete picture of radio operations:

One-to-Many Relationships

• A client can have multiple contacts (clients → client_contacts)
• A client can have multiple invoices (clients → invoices)
• An invoice can have multiple line items (invoices → invoice_items)
• A program can have multiple scheduled instances (programs → program_schedules)
• A user can create multiple content items (users → content_items)

Many-to-Many Relationships

• Programs and hosts (programs ↔ users via program_hosts)
• Roles and permissions (roles ↔ permissions via role_permissions)
• Content items and broadcast schedules (content_items ↔ program_schedules via content_schedules)

Data Integrity Constraints

The BORMS database implements several data integrity constraints to ensure information consistency:

• Primary Keys: Each table has a unique identifier to ensure record uniqueness
• Foreign Keys: Relationships between tables are enforced to maintain referential integrity
• Unique Constraints: Fields like email addresses and invoice numbers are kept unique
• Not Null Constraints: Critical fields are required to have values
• Enumerations: Predefined values for status fields and types to ensure data consistency
• Default Values: Sensible defaults to reduce data entry errors
• Timestamps: Automatic tracking of record creation and updates

Database Backup and Recovery

BORMS includes a robust database backup and recovery strategy:

1. Automated Daily Backups: Full database dumps every 24 hours
2. Transactional Logs: Continuous logging of database changes for point-in-time recovery
3. Offsite Storage: Database backups stored both locally and in secure cloud storage
4. Recovery Testing: Regular restoration tests to verify backup integrity
5. Retention Policy: Daily backups kept for 30 days, weekly backups for 3 months, monthly backups for 1 year

Database Access Control

Access to the database is strictly controlled based on user roles:

Data Entry Guidelines

To maintain data quality and consistency, follow these guidelines when entering data:

1. Client Names: Use official registered business names; capitalize properly
2. Contact Information: Verify email addresses and phone numbers for accuracy
3. Dates: Use consistent YYYY-MM-DD format for all date fields
4. Monetary Values: Always enter amounts in the local currency without the currency symbol
5. Notes: Be concise, factual, and professional in all system notes