CMD Pro Database Design

A Robust, Scalable, and Secure Foundation

📅 Last Updated: August 2026 ⏱️ Reading Time: 14 minutes 📋 Schema Version: 1.1

The CMD Pro database design is built upon WordPress best practices, utilizing the $wpdb class for secure database operations and following a normalized structure to ensure data integrity, performance, and scalability. The design employs a combination of WordPress core tables (for users, posts, options) and custom tables for CMD Pro-specific data.

📑 Table of Contents

▶ Design Principles ▶ Schema Overview ▶ Table Specifications ▶ ER Diagram ▶ Maintenance & Migration ▶ Sample Queries ▶ Performance Benchmarks ▶ Future Expansion

15.1. Database Design Principles

📋 WordPress Standards All tables use the WordPress table prefix (wp_) and follow WordPress coding standards.

📊 Normalization Tables are normalized to 3rd Normal Form (3NF) to eliminate data redundancy and ensure data integrity.

⚡ Performance Optimization Appropriate indexes are implemented on frequently queried columns.

🔒 Security All queries use $wpdb->prepare() to prevent SQL injection attacks.

📈 Scalability Table structures are designed to handle large volumes of historical data efficiently.

🔌 Extensibility Tables include flexible fields (e.g., metadata) to accommodate future features without schema changes.

15.2. Database Schema Overview

🗄️ CMD PRO DATABASE SCHEMA

📦 WordPress Core Tables (Existing)

👤 wp_users 📄 wp_posts ⚙️ wp_options 📝 wp_postmeta

▼

🔧 CMD Pro Custom Tables

📊 wp_cmdp_prices 📈 wp_cmdp_history 🔔 wp_cmdp_alerts 📝 wp_cmdp_logs ⚡ wp_cmdp_api_cache

Storage Engine InnoDB

Character Set utf8mb4

Collation utf8mb4_unicode_ci

Transactions ✅ Supported

Foreign Keys ✅ Enabled

Estimated Records 100,000+

📊

Database at a Glance

5 Custom Tables

40+ Fields Total

12+ Indexes

6 Primary Keys

5 Foreign Keys

100K+ Optimized for Records

15.3. Table Specifications

📊 wp_cmdp_prices 📊 Current Prices

Purpose: Stores the current (latest) price data for all tracked commodities. This table serves as the primary source for displaying live prices on the dashboard.

Description: This table contains a single record per commodity, representing the most recent price data fetched from the API or manually entered by the administrator. It includes both USD and PKR prices, along with metadata about the data source and update time.

Field Name	Data Type	Length	Null	Default	Description
price_id	BIGINT(20)	—	NO	Auto Increment	Primary Key – Unique identifier for each price record
commodity_code	VARCHAR(20)	20	NO	—	Unique commodity identifier (e.g., ‘COTTON’, ‘OILSEED’, ‘SUGAR’)
commodity_name	VARCHAR(100)	100	NO	—	Human-readable commodity name
usd_price	DECIMAL(12,6)	—	NO	0.000000	Price in USD per unit
pkr_price	DECIMAL(15,2)	—	NO	0.00	Price in PKR per Maund (converted automatically)
unit	VARCHAR(20)	20	NO	‘lb’	Unit of measurement (e.g., ‘lb’, ‘ton’, ‘kg’)
conversion_factor	DECIMAL(10,4)	—	YES	NULL	Factor used to convert USD price to PKR/Maund
exchange_rate	DECIMAL(10,4)	—	YES	NULL	Exchange rate used at the time of conversion (PKR/USD)
source	VARCHAR(50)	50	NO	‘manual’	Data source (e.g., ‘api_investing’, ‘api_alphavantage’, ‘manual’)
high	DECIMAL(12,6)	—	YES	NULL	Highest price recorded for the period
low	DECIMAL(12,6)	—	YES	NULL	Lowest price recorded for the period
change_percent	DECIMAL(8,4)	—	YES	NULL	Percentage change from the previous price
updated_at	DATETIME	—	NO	CURRENT_TIMESTAMP	Timestamp of the last price update
metadata	JSON	—	YES	NULL	Flexible JSON field for additional attributes

Index Name	Column(s)	Type	Description
PRIMARY	price_id	PRIMARY	Unique identifier for each record
idx_commodity_code	commodity_code	INDEX	Fast lookup by commodity code
idx_updated_at	updated_at	INDEX	Efficient sorting and filtering by update time
idx_source	source	INDEX	Filtering by data source

SQL DDL

CREATE TABLE IF NOT EXISTS wp_cmdp_prices (
    price_id BIGINT(20) NOT NULL AUTO_INCREMENT,
    commodity_code VARCHAR(20) NOT NULL,
    commodity_name VARCHAR(100) NOT NULL,
    usd_price DECIMAL(12,6) NOT NULL DEFAULT 0.000000,
    pkr_price DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    unit VARCHAR(20) NOT NULL DEFAULT 'lb',
    conversion_factor DECIMAL(10,4) DEFAULT NULL,
    exchange_rate DECIMAL(10,4) DEFAULT NULL,
    source VARCHAR(50) NOT NULL DEFAULT 'manual',
    high DECIMAL(12,6) DEFAULT NULL,
    low DECIMAL(12,6) DEFAULT NULL,
    change_percent DECIMAL(8,4) DEFAULT NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    metadata JSON DEFAULT NULL,
    PRIMARY KEY (price_id),
    KEY idx_commodity_code (commodity_code),
    KEY idx_updated_at (updated_at),
    KEY idx_source (source)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

📈 wp_cmdp_history 📈 Historical Data

Purpose: Stores historical price data for all commodities, enabling trend analysis, chart rendering, and back-testing.

Description: This table records price snapshots at regular intervals (e.g., daily) or whenever a price change occurs. It stores both USD and PKR prices, along with the exchange rate used at the time of recording. This table is the primary source for chart data.

Field Name	Data Type	Length	Null	Default	Description
history_id	BIGINT(20)	—	NO	Auto Increment	Primary Key – Unique identifier for each historical record
commodity_code	VARCHAR(20)	20	NO	—	Unique commodity identifier
commodity_name	VARCHAR(100)	100	NO	—	Human-readable commodity name
usd_price	DECIMAL(12,6)	—	NO	0.000000	Price in USD per unit at the time of recording
pkr_price	DECIMAL(15,2)	—	NO	0.00	Price in PKR per Maund at the time of recording
unit	VARCHAR(20)	20	NO	‘lb’	Unit of measurement
exchange_rate	DECIMAL(10,4)	—	NO	0.0000	Exchange rate (PKR/USD) used for conversion
volume	DECIMAL(15,2)	—	YES	NULL	Trading volume (if available)
recorded_at	DATETIME	—	NO	CURRENT_TIMESTAMP	Timestamp when the price was recorded
source	VARCHAR(50)	50	NO	‘manual’	Data source
metadata	JSON	—	YES	NULL	Flexible JSON field for additional data

Index Name	Column(s)	Type	Description
PRIMARY	history_id	PRIMARY	Unique identifier for each record
idx_commodity_code	commodity_code	INDEX	Fast lookup by commodity code
idx_recorded_at	recorded_at	INDEX	Efficient sorting and filtering by time
idx_commodity_time	commodity_code, recorded_at	COMPOSITE	Optimized queries for time-series data

SQL DDL

CREATE TABLE IF NOT EXISTS wp_cmdp_history (
    history_id BIGINT(20) NOT NULL AUTO_INCREMENT,
    commodity_code VARCHAR(20) NOT NULL,
    commodity_name VARCHAR(100) NOT NULL,
    usd_price DECIMAL(12,6) NOT NULL DEFAULT 0.000000,
    pkr_price DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    unit VARCHAR(20) NOT NULL DEFAULT 'lb',
    exchange_rate DECIMAL(10,4) NOT NULL DEFAULT 0.0000,
    volume DECIMAL(15,2) DEFAULT NULL,
    recorded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    source VARCHAR(50) NOT NULL DEFAULT 'manual',
    metadata JSON DEFAULT NULL,
    PRIMARY KEY (history_id),
    KEY idx_commodity_code (commodity_code),
    KEY idx_recorded_at (recorded_at),
    KEY idx_commodity_time (commodity_code, recorded_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

🔔 wp_cmdp_alerts 🔔 Alerts

Purpose: Manages user-defined price alerts, allowing users to receive notifications when commodity prices reach specified thresholds.

Description: This table stores alert configurations created by users. Each alert is associated with a specific user, commodity, and threshold type (e.g., “price above X” or “price below Y”). The system checks these alerts against live prices and triggers notifications accordingly.

Field Name	Data Type	Length	Null	Default	Description
alert_id	BIGINT(20)	—	NO	Auto Increment	Primary Key – Unique identifier for each alert
user_id	BIGINT(20)	—	NO	—	Foreign Key – WordPress user ID (`wp_users.ID`)
commodity_code	VARCHAR(20)	20	NO	—	Commodity to monitor
alert_type	ENUM(‘above’,’below’,’change’)	—	NO	‘above’	Type of alert: above threshold, below threshold, or percentage change
threshold	DECIMAL(12,6)	—	NO	0.000000	Price threshold value (in USD or PKR)
currency_type	ENUM(‘USD’,’PKR’)	—	NO	‘USD’	Currency in which the threshold is defined
notification_method	VARCHAR(50)	50	NO	’email’	Notification method (e.g., ’email’, ‘dashboard’, ‘webhook’)
is_active	TINYINT(1)	—	NO	1	Alert status: 1 = active, 0 = inactive
triggered_at	DATETIME	—	YES	NULL	Timestamp when the alert was last triggered
created_at	DATETIME	—	NO	CURRENT_TIMESTAMP	Timestamp when the alert was created
updated_at	DATETIME	—	YES	NULL	Timestamp when the alert was last modified
metadata	JSON	—	YES	NULL	Additional alert settings (e.g., cooldown period)

Index Name	Column(s)	Type	Description
PRIMARY	alert_id	PRIMARY	Unique identifier for each record
idx_user_id	user_id	INDEX	Fast lookup by user
idx_commodity_code	commodity_code	INDEX	Filtering by commodity
idx_user_commodity	user_id, commodity_code	COMPOSITE	Optimized queries for user-commodity alerts
idx_is_active	is_active	INDEX	Efficient filtering of active alerts

SQL DDL

CREATE TABLE IF NOT EXISTS wp_cmdp_alerts (
    alert_id BIGINT(20) NOT NULL AUTO_INCREMENT,
    user_id BIGINT(20) NOT NULL,
    commodity_code VARCHAR(20) NOT NULL,
    alert_type ENUM('above', 'below', 'change') NOT NULL DEFAULT 'above',
    threshold DECIMAL(12,6) NOT NULL DEFAULT 0.000000,
    currency_type ENUM('USD', 'PKR') NOT NULL DEFAULT 'USD',
    notification_method VARCHAR(50) NOT NULL DEFAULT 'email',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    triggered_at DATETIME DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT NULL,
    metadata JSON DEFAULT NULL,
    PRIMARY KEY (alert_id),
    KEY idx_user_id (user_id),
    KEY idx_commodity_code (commodity_code),
    KEY idx_user_commodity (user_id, commodity_code),
    KEY idx_is_active (is_active),
    CONSTRAINT fk_alert_user_id FOREIGN KEY (user_id) REFERENCES wp_users (ID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

📝 wp_cmdp_logs 📝 Audit Logs

Purpose: Provides an audit trail of all user actions, system events, and data changes for security, debugging, and compliance purposes.

Description: This table records every significant action performed within the plugin, including price updates, settings changes, alert creations, and user logins. The log data is useful for troubleshooting, security auditing, and understanding user behavior.

📋 Example Record
User: 1 | Action: settings_change | Entity: currency | Details: {“old”: 278.50, “new”: 280.00} | IP: 192.168.1.1 | Created: 2026-08-06 10:15:00

Field Name	Data Type	Length	Null	Default	Description
log_id	BIGINT(20)	—	NO	Auto Increment	Primary Key – Unique identifier for each log entry
user_id	BIGINT(20)	—	YES	NULL	Foreign Key – WordPress user ID who performed the action (NULL for system actions)
action	VARCHAR(50)	50	NO	—	Action performed (e.g., ‘price_update’, ‘settings_change’, ‘alert_created’)
entity	VARCHAR(50)	50	NO	—	Entity type affected (e.g., ‘price’, ‘alert’, ‘settings’, ‘user’)
entity_id	VARCHAR(100)	100	YES	NULL	Identifier of the affected entity (e.g., commodity_code, alert_id)
details	JSON	—	YES	NULL	Detailed information about the action (old values, new values, changes)
ip_address	VARCHAR(45)	45	YES	NULL	IP address of the user who performed the action
user_agent	VARCHAR(255)	255	YES	NULL	User agent of the browser/client
created_at	DATETIME	—	NO	CURRENT_TIMESTAMP	Timestamp when the log entry was created

Index Name	Column(s)	Type	Description
PRIMARY	log_id	PRIMARY	Unique identifier for each record
idx_user_id	user_id	INDEX	Filtering by user
idx_action	action	INDEX	Filtering by action type
idx_entity	entity, entity_id	COMPOSITE	Filtering by entity
idx_created_at	created_at	INDEX	Time-based queries

SQL DDL

CREATE TABLE IF NOT EXISTS wp_cmdp_logs (
    log_id BIGINT(20) NOT NULL AUTO_INCREMENT,
    user_id BIGINT(20) DEFAULT NULL,
    action VARCHAR(50) NOT NULL,
    entity VARCHAR(50) NOT NULL,
    entity_id VARCHAR(100) DEFAULT NULL,
    details JSON DEFAULT NULL,
    ip_address VARCHAR(45) DEFAULT NULL,
    user_agent VARCHAR(255) DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (log_id),
    KEY idx_user_id (user_id),
    KEY idx_action (action),
    KEY idx_entity (entity, entity_id),
    KEY idx_created_at (created_at),
    CONSTRAINT fk_log_user_id FOREIGN KEY (user_id) REFERENCES wp_users (ID) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

⚡ wp_cmdp_api_cache ⚡ API Cache

Purpose: Stores cached API responses to reduce external API calls, improve performance, and provide fallback data during API outages.

Description: This table holds the raw responses from external commodity data APIs. Each response is stored with a unique cache key, expiration time, and usage statistics. The cache manager uses this table to serve data quickly without making redundant API calls.

Field Name	Data Type	Length	Null	Default	Description
cache_id	BIGINT(20)	—	NO	Auto Increment	Primary Key – Unique identifier for each cache entry
cache_key	VARCHAR(255)	255	NO	—	Unique key for the cached data (hash of endpoint + parameters)
endpoint	VARCHAR(255)	255	NO	—	API endpoint URL that was called
parameters	JSON	—	YES	NULL	Parameters used in the API request
response	LONGTEXT	—	NO	—	Cached API response (JSON string)
response_code	INT(11)	—	YES	NULL	HTTP response code (e.g., 200, 404)
expires_at	DATETIME	—	NO	—	Timestamp when the cache entry expires
created_at	DATETIME	—	NO	CURRENT_TIMESTAMP	Timestamp when the cache entry was created
hits	INT(11)	—	NO	0	Number of times this cache entry was served
last_accessed_at	DATETIME	—	YES	NULL	Timestamp when the cache entry was last accessed

Index Name	Column(s)	Type	Description
PRIMARY	cache_id	PRIMARY	Unique identifier for each record
idx_cache_key	cache_key	UNIQUE	Fast lookup by unique cache key
idx_expires_at	expires_at	INDEX	Efficient expiration cleanup
idx_endpoint	endpoint	INDEX	Filtering by API endpoint

SQL DDL

CREATE TABLE IF NOT EXISTS wp_cmdp_api_cache (
    cache_id BIGINT(20) NOT NULL AUTO_INCREMENT,
    cache_key VARCHAR(255) NOT NULL,
    endpoint VARCHAR(255) NOT NULL,
    parameters JSON DEFAULT NULL,
    response LONGTEXT NOT NULL,
    response_code INT(11) DEFAULT NULL,
    expires_at DATETIME NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    hits INT(11) NOT NULL DEFAULT 0,
    last_accessed_at DATETIME DEFAULT NULL,
    PRIMARY KEY (cache_id),
    UNIQUE KEY idx_cache_key (cache_key),
    KEY idx_expires_at (expires_at),
    KEY idx_endpoint (endpoint)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

15.4. Entity Relationship (ER) Diagram

🔗 ENTITY RELATIONSHIP DIAGRAM

👤 wp_users WordPress Users

│

1 : Many

├─────────────┐

🔔 wp_cmdp_alerts User Alerts

📝 wp_cmdp_logs Audit Logs

📋 Future Tables Portfolio, Watchlist

│

commodity_code

▼

📊 wp_cmdp_prices Current Prices

📈 wp_cmdp_history Historical Prices

⚡ wp_cmdp_api_cache API Cache

Core Tables Alerts Logs Prices History Cache Future 🔑 PK = Primary Key 🔗 FK = Foreign Key ━━▶ One-to-Many

15.5. Database Maintenance

Task	Description	Frequency
Table Optimization	Run `OPTIMIZE TABLE` to defragment tables and reclaim unused space	📅 Monthly
Cache Cleanup	Delete expired cache entries from `wp_cmdp_api_cache`	📅 Daily
Historical Data Archival	Move older records from `wp_cmdp_history` to an archive table to maintain performance	📅 Quarterly (for data > 2 years)
Log Rotation	Purge log entries older than 90 days to prevent table bloat	📅 Weekly
Backup	Full database backup for disaster recovery	📅 Daily
Index Rebuild	Rebuild indexes after bulk data operations	📅 As needed

Automatic Cleanup Flow

🧹 AUTOMATIC CLEANUP FLOW

⏰ Cron

▼

🗑️ Expired Cache

▼

❌ Delete

▼

🔄 Rebuild Index

▼

💾 Backup

▼

✅ Done

15.6. Database Migration Strategy

Phase	Action	Description
1. Schema Creation	Run DDL scripts	Create all custom tables during plugin activation
2. Data Migration	Data import	Migrate initial commodity definitions and sample data
3. Versioning	Version tracking	Use WordPress options to track database schema version
4. Upgrades	Migration scripts	Provide upgrade scripts for future schema changes
5. Rollback	Backup restoration	Ensure ability to rollback to previous schema if needed

15.7. Sample SQL Queries

Get Latest Price for a Specific Commodity

SELECT * FROM wp_cmdp_prices WHERE commodity_code = ‘COTTON’ LIMIT 1;

Get Historical Prices for Chart Data

SELECT recorded_at, usd_price, pkr_price FROM wp_cmdp_history WHERE commodity_code = ‘COTTON’ AND recorded_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) ORDER BY recorded_at ASC;

Get Active Alerts for a User

SELECT * FROM wp_cmdp_alerts WHERE user_id = 123 AND is_active = 1;

Clean Expired Cache Entries

DELETE FROM wp_cmdp_api_cache WHERE expires_at < NOW();

Get Top Gainers (Last 24 Hours)

SELECT commodity_name, change_percent FROM wp_cmdp_prices WHERE change_percent IS NOT NULL ORDER BY change_percent DESC LIMIT 5;

Get Weekly Average Price

SELECT AVG(usd_price) AS weekly_avg FROM wp_cmdp_history WHERE commodity_code = ‘COTTON’ AND recorded_at >= DATE_SUB(NOW(), INTERVAL 7 DAY);

Get Most Viewed Commodity

SELECT commodity_code, COUNT(*) AS view_count FROM wp_cmdp_logs WHERE action = ‘view_price’ GROUP BY commodity_code ORDER BY view_count DESC LIMIT 1;

15.8. Performance Benchmarks

The following benchmarks represent target performance goals for the database layer under normal operating conditions. These targets ensure a responsive user experience and efficient data handling.

3 ms Latest Price Query

12 ms History Query (30 days)

< 1 ms Cached Query

45 ms Chart Data Generation

18 MB Memory Usage

100K+ Records Optimized

15.9. Future Database Expansion

The database schema is designed to evolve. The following tables are planned for future releases to support new features and capabilities:

🤖

wp_cmdp_predictions AI Forecasts

📰

wp_cmdp_news Commodity News

📂

wp_cmdp_portfolio User Portfolio

⭐

wp_cmdp_watchlist Favorites

📱

wp_cmdp_notifications Push Notifications

📊

wp_cmdp_reports Analytics

🗄️

Database Design Conclusion

This comprehensive database design provides a robust, scalable, and secure foundation for the CMD Pro plugin. With five custom tables, optimized indexes, and secure query practices, the design ensures efficient data storage and retrieval for all core features while maintaining flexibility for future enhancements. The schema is optimized for 100,000+ records and supports InnoDB transactions and foreign key constraints.

🗄️ This comprehensive database design provides a robust, scalable, and secure foundation for the CMD Pro plugin, ensuring efficient data storage and retrieval for all core features.

✅ Document Status: Approved for Development 📋 Schema Version: 1.1 📅 Last Updated: August 2026