Database

SLAED CMS uses a relational MySQL/MariaDB database to store all system information. The database structure is designed with performance, security, and scalability in mind.

General Architecture

All tables in the database use a common prefix that is set during system installation. By default, the prefix is sl_.

Table Prefix

The table prefix allows multiple SLAED CMS installations to coexist in the same database. This is particularly useful for development and testing environments.

Main Tables

SLAED CMS database consists of several core tables that store system information:

Table Structure

Each table is designed with specific fields to store relevant information efficiently.

sl_modules - System Modules

Stores information about all system modules:

CREATE TABLE `sl_modules` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(50) NOT NULL DEFAULT '',
  `description` text NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '0',
  `view` tinyint(1) NOT NULL DEFAULT '0',
  `blocks` tinyint(1) NOT NULL DEFAULT '1',
  `blocks_c` tinyint(1) NOT NULL DEFAULT '1',
  `mod_group` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `title` (`title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Fields:

• id - Unique module identifier
• title - Module name (unique)
• description - Module description
• active - Activity status (0/1)
• view - Module visibility
• blocks - Block usage
• blocks_c - Central block usage
• mod_group - Module group

sl_config - System Configuration

Stores all system settings:

CREATE TABLE `sl_config` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT '',
  `value` text NOT NULL,
  `module` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_module` (`name`,`module`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Fields:

• id - Unique setting identifier
• name - Parameter name
• value - Parameter value
• module - Module the parameter belongs to

sl_users - System Users

Stores information about registered users:

CREATE TABLE `sl_users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(25) NOT NULL DEFAULT '',
  `user_email` varchar(100) NOT NULL DEFAULT '',
  `user_password` varchar(255) NOT NULL DEFAULT '',
  `user_group` tinyint(1) NOT NULL DEFAULT '1',
  `user_avatar` varchar(100) NOT NULL DEFAULT 'default/00.gif',
  `user_regdate` int(11) NOT NULL DEFAULT '0',
  `user_lastvisit` int(11) NOT NULL DEFAULT '0',
  `user_active` tinyint(1) NOT NULL DEFAULT '0',
  `user_level` tinyint(1) NOT NULL DEFAULT '1',
  `user_sig` text NOT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `user_name` (`user_name`),
  UNIQUE KEY `user_email` (`user_email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Fields:

• user_id - Unique user identifier
• user_name - Username (unique)
• user_email - User email (unique)
• user_password - Hashed password
• user_group - User group
• user_avatar - User avatar
• user_regdate - Registration date (timestamp)
• user_lastvisit - Last visit date (timestamp)
• user_active - Activity status (0/1)
• user_level - User level
• user_sig - User signature

Indexing and Optimization

For high performance, the following indexes are used:

Main Indexes

• Primary keys for all tables
• Unique indexes for fields with unique values
• Composite indexes for frequently used field combinations

Optimization Recommendations

1. Use proper data types - Use minimal necessary field sizes
2. Indexes - Create indexes for fields used in WHERE, ORDER BY, JOIN
3. Normalization - Follow database normalization principles

Database Security

SLAED CMS uses prepared statements to protect against SQL injections:

// Correct - prepared statement
$stmt = $db->prepare("SELECT * FROM sl_users WHERE user_id = ?");
$stmt->bind_param("i", $user_id);
$stmt->execute();

// Incorrect - direct substitution (vulnerable)
$query = "SELECT * FROM sl_users WHERE user_id = " . $user_id;

Database Access Rights

It is recommended to create a separate database user with limited rights:

-- Create user with minimal necessary rights
CREATE USER 'slaed_user'@'localhost' IDENTIFIED BY 'strong_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON slaed_cms.* TO 'slaed_user'@'localhost';
FLUSH PRIVILEGES;

Backup and Recovery

SLAED CMS supports automatic backup of structure and data:

-- Backup table structure
SHOW CREATE TABLE sl_users;

-- Backup data
SELECT * FROM sl_users INTO OUTFILE '/backup/users_backup.sql';

Data Recovery

-- Restore structure
SOURCE /backup/users_structure.sql;

-- Restore data
LOAD DATA INFILE '/backup/users_backup.sql' INTO TABLE sl_users;

Database Migration

For managing database structure changes, a migration system is used:

-- Example migration to add a new field
ALTER TABLE sl_users ADD COLUMN user_phone VARCHAR(20) DEFAULT '' AFTER user_email;

Monitoring and Maintenance

Regular analysis of queries helps identify bottlenecks:

-- Analyze slow queries
SHOW PROCESSLIST;

-- Index usage statistics
SHOW INDEX FROM sl_users;

-- Table size statistics
SELECT 
    table_name AS `Table`,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
WHERE table_schema = 'slaed_cms'
ORDER BY (data_length + index_length) DESC;

Regular table optimization improves performance:

-- Optimize tables
OPTIMIZE TABLE sl_users, sl_modules, sl_config;