Database Schema

Overview

The Dashboard Dilemas database uses a relational MySQL schema with foreign key constraints to maintain data integrity across multi-tenant organizations. All tables use the de_app_ prefix for namespace isolation. Schema Characteristics:

Character Set: utf8mb4 with unicode collation
Storage Engine: InnoDB (ACID compliance, foreign keys)
Naming Convention: Snake_case for columns, PascalCase for legacy WordPress compatibility
ID Strategy: VARCHAR(50) for flexibility with custom identifiers

Entity-Relationship Diagram

The following ER diagram illustrates the core database structure and relationships:

Core Tables

de_app_clients

Stores client organizations (companies) that use the platform.

Column	Type	Constraints	Description
`id`	VARCHAR(50)	PRIMARY KEY	Unique client identifier
`name`	VARCHAR(100)	NOT NULL	Client/company name
`created_at`	DATETIME	DEFAULT CURRENT_TIMESTAMP	Record creation timestamp

Relationships:

One client has many areas (1:N with de_app_areas)
One client has many users (1:N with de_app_users)
One client has many games (1:N with de_app_games)

Example Data:

INSERT INTO de_app_clients (id, name) VALUES
('client_a', 'Acme Corporation'),
('client_b', 'Tech Innovators Inc.');

de_app_areas

Departments or organizational units within a client company.

Column	Type	Constraints	Description
`id`	VARCHAR(50)	PRIMARY KEY	Unique area identifier
`name`	VARCHAR(100)	NOT NULL	Area/department name
`color`	VARCHAR(20)	DEFAULT ‘#000000’	UI color for visualization

Relationships:

Each area belongs to one client (via implicit client association)
One area has many users (1:N with de_app_users)

Example Data:

INSERT INTO de_app_areas (id, name, color) VALUES
('area_tech', 'Tecnología', '#3b82f6'),
('area_hr', 'Recursos Humanos', '#ef4444'),
('area_fin', 'Finanzas', '#10b981'),
('area_ops', 'Operaciones', '#f59e0b');

The color field uses hex color codes for consistent area visualization in analytics dashboards.

de_app_users

Participant users who engage with ethical dilemma scenarios.

Column	Type	Constraints	Description
`id`	VARCHAR(50)	PRIMARY KEY	Unique user identifier
`name`	VARCHAR(100)	NOT NULL	Full name
`email`	VARCHAR(100)	NOT NULL	Email address
`avatar`	VARCHAR(10)	DEFAULT ’👤‘	Emoji avatar for UI
`area`	VARCHAR(100)	NULL	Department assignment
`createdAt`	DATETIME	DEFAULT CURRENT_TIMESTAMP	Account creation
`lastActive`	DATETIME	DEFAULT CURRENT_TIMESTAMP	Last activity timestamp

Relationships:

Each user belongs to one client (implicit via area or direct FK)
Each user belongs to one area (N:1 with de_app_areas)
One user has many sessions (1:N with de_app_sessions)
One user has many answers (1:N with de_app_answers)

Example Data:

INSERT INTO de_app_users (id, name, email, avatar, area) VALUES
('u1', 'Carlos Mendez', '[email protected]', '👨', 'Ventas'),
('u2', 'Ana Lopez', '[email protected]', '👩', 'Marketing');

The area column contains denormalized string values for performance. In a normalized design, this would be a foreign key to de_app_areas.id.

de_app_games

Ethical dilemma scenarios (gamified question sets).

Column	Type	Constraints	Description
`id`	VARCHAR(50)	PRIMARY KEY	Unique game identifier
`name`	VARCHAR(100)	NOT NULL	Game/dilemma title
`category`	VARCHAR(50)	NOT NULL	Classification (Logic, Tech, Business, etc.)
`client`	VARCHAR(100)	NULL	Client owner
`area`	VARCHAR(100)	NULL	Target department
`icon`	VARCHAR(50)	DEFAULT ’🎮‘	Emoji icon
`clientLogo`	VARCHAR(255)	NULL	Logo URL

Relationships:

Each game belongs to one client
One game has many questions (1:N with de_app_game_questions)
One game has many sessions (1:N with de_app_sessions)

Example Data:

INSERT INTO de_app_games (id, name, category, client, icon, area) VALUES
('game_1', 'Dilema del Puente', 'Logic', 'Cliente A', '🌉', 'Ventas'),
('game_2', 'Ética en la IA', 'Tech', 'Cliente B', '🤖', 'Marketing');

de_app_game_questions

Individual questions within a game/dilemma scenario.

Column	Type	Constraints	Description
`id`	INT	PRIMARY KEY AUTO_INCREMENT	Question ID
`game_id`	VARCHAR(50)	FOREIGN KEY → de_app_games.id	Parent game
`question_text`	TEXT	NOT NULL	Question content
`order_num`	INT	NOT NULL	Display sequence
`difficulty`	VARCHAR(20)	NULL	Difficulty level
`correct_answer`	TEXT	NULL	Expected answer

Relationships:

Each question belongs to one game (N:1 with de_app_games)
One question has many answers (1:N with de_app_answers)

Questions are ordered using order_num to control presentation sequence in the game flow.

de_app_sessions

User gameplay sessions tracking participation and performance.

Column	Type	Constraints	Description
`id`	VARCHAR(50)	PRIMARY KEY	Session identifier
`userId`	VARCHAR(50)	FOREIGN KEY → de_app_users.id	Participant
`gameId`	VARCHAR(50)	FOREIGN KEY → de_app_games.id	Game played
`score`	INT	DEFAULT 0	Achieved score
`duration`	INT	DEFAULT 0	Time spent (minutes)
`startedAt`	DATETIME	DEFAULT CURRENT_TIMESTAMP	Session start
`endedAt`	DATETIME	DEFAULT CURRENT_TIMESTAMP	Session end

Foreign Key Constraints:

CONSTRAINT fk_session_user FOREIGN KEY (userId) 
    REFERENCES de_app_users(id) ON DELETE CASCADE,
CONSTRAINT fk_session_game FOREIGN KEY (gameId) 
    REFERENCES de_app_games(id) ON DELETE CASCADE

Example Data:

INSERT INTO de_app_sessions (id, userId, gameId, score, duration) VALUES
('s1', 'u1', 'game_1', 850, 15),
('s2', 'u2', 'game_1', 920, 12);

The ON DELETE CASCADE constraint ensures session data is automatically cleaned up when users or games are deleted.

de_app_answers

Individual question responses from users during sessions.

Column	Type	Constraints	Description
`id`	VARCHAR(50)	PRIMARY KEY	Answer identifier
`sessionId`	VARCHAR(50)	FOREIGN KEY → de_app_sessions.id	Session context
`questionId`	INT	NULL	Question reference
`responseValue`	INT	NULL	Answer score/value
`responseTime`	INT	NULL	Time taken (seconds)

Relationships:

Each answer belongs to one session
Each answer relates to one question

Example Data:

INSERT INTO de_app_answers (id, sessionId, questionId, responseValue, responseTime) VALUES
('am_1', 'sm_1', 1, 100, 5),
('am_2', 'sm_7', 1, 40, 15);

Authentication Tables

These tables support WordPress-compatible authentication:

de_users

Core user authentication table (WordPress compatible).

Column	Type	Constraints	Description
`ID`	BIGINT	PRIMARY KEY AUTO_INCREMENT	User ID
`user_login`	VARCHAR(60)	NOT NULL UNIQUE	Username
`user_pass`	VARCHAR(255)	NOT NULL	Hashed password
`user_email`	VARCHAR(100)	NOT NULL	Email address
`display_name`	VARCHAR(250)	NOT NULL	Display name
`user_activation_key`	VARCHAR(255)	NULL	Password reset token
`user_registered`	DATETIME	DEFAULT CURRENT_TIMESTAMP	Registration date

Password Hashing:

Primary: WordPress Phpass (8 rounds)
Fallback: Bcrypt compatibility

de_usermeta

User metadata for roles and capabilities.

Column	Type	Constraints	Description
`umeta_id`	BIGINT	PRIMARY KEY AUTO_INCREMENT	Meta ID
`user_id`	BIGINT	FOREIGN KEY → de_users.ID	User reference
`meta_key`	VARCHAR(255)	NOT NULL	Metadata key
`meta_value`	LONGTEXT	NULL	Serialized value

Common Meta Keys:

de_capabilities: Serialized role array
wp_capabilities: WordPress roles (fallback)

Example Capabilities:

a:1:{s:13:"administrator";b:1;}
// Unserializes to: ['administrator' => true]

Indexes and Performance

Primary Indexes

All tables use primary keys for efficient row lookup:

PRIMARY KEY (id)  -- Most tables
PRIMARY KEY (ID)  -- WordPress-compatible tables

Foreign Key Indexes

Automatic indexes on foreign key columns:

KEY userId (userId)  -- de_app_sessions
KEY gameId (gameId)  -- de_app_sessions

Recommended Additional Indexes

For production optimization:

-- User lookups by email
CREATE INDEX idx_user_email ON de_app_users(email);

-- Session queries by date range
CREATE INDEX idx_session_dates ON de_app_sessions(startedAt, endedAt);

-- Game filtering by client
CREATE INDEX idx_game_client ON de_app_games(client);

Data Integrity

Foreign Key Constraints

The schema enforces referential integrity:

CASCADE DELETE: Sessions are deleted when users or games are removed
RESTRICT: Prevents deletion of areas with assigned users (implementation-specific)

Data Validation

Application Layer:

Email format validation via PHP filter functions
Username uniqueness enforced by UNIQUE constraint
Required fields validated before INSERT

Database Layer:

NOT NULL constraints on critical columns
DEFAULT values for timestamps and UI elements
CHECK constraints for value ranges (MySQL 8.0+)

Schema Migration

The database includes conditional schema updates:

-- Add column only if it doesn't exist
SET @preparedStatement = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
   WHERE table_name = 'de_app_users' AND column_name = 'area') > 0,
  "SELECT 1",
  "ALTER TABLE de_app_users ADD area VARCHAR(100) DEFAULT NULL;"
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;

This pattern allows safe re-execution of migration scripts without duplicate column errors.

Sample Queries

Get User Participation Summary

SELECT 
    u.name,
    u.email,
    u.area,
    COUNT(s.id) as sessions_completed,
    AVG(s.score) as average_score,
    SUM(s.duration) as total_minutes
FROM de_app_users u
LEFT JOIN de_app_sessions s ON u.id = s.userId
GROUP BY u.id
ORDER BY average_score DESC;

Game Performance by Area

SELECT 
    g.name as game_name,
    u.area,
    COUNT(s.id) as participants,
    AVG(s.score) as avg_score,
    AVG(s.duration) as avg_duration
FROM de_app_sessions s
JOIN de_app_games g ON s.gameId = g.id
JOIN de_app_users u ON s.userId = u.id
GROUP BY g.id, u.area
ORDER BY g.name, avg_score DESC;

Find Users with Failed Questions

SELECT 
    u.name,
    a.questionId,
    a.responseValue,
    a.responseTime
FROM de_app_answers a
JOIN de_app_sessions s ON a.sessionId = s.id
JOIN de_app_users u ON s.userId = u.id
WHERE a.responseValue < 70  -- Below passing threshold
ORDER BY u.name, a.questionId;

Database Initialization

To set up the database:

Create Database:

CREATE DATABASE dilemas CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Import Schema:

mysql -u root -p dilemas < sql/database.sql

Import Users (Optional):

mysql -u root -p dilemas < sql/users.sql

Apply Area Schema:

mysql -u root -p dilemas < sql/areas_schema.sql

Next Steps

Technical Architecture Overview - System components and infrastructure
Authentication & Sessions - Security implementation details

Technical Overview

Development

Overview

Entity-Relationship Diagram

Core Tables

de_app_clients

de_app_areas

de_app_users

de_app_games

de_app_game_questions

de_app_sessions

de_app_answers

Authentication Tables

de_users

de_usermeta

Indexes and Performance

Primary Indexes

Foreign Key Indexes

Recommended Additional Indexes

Data Integrity

Foreign Key Constraints

Data Validation

Schema Migration

Sample Queries

Get User Participation Summary

Game Performance by Area

Find Users with Failed Questions

Database Initialization

Next Steps

Build docs developers (and LLMs) love

Technical Overview

Development

​Overview

​Entity-Relationship Diagram

​Core Tables

​de_app_clients

​de_app_areas

​de_app_users

​de_app_games

​de_app_game_questions

​de_app_sessions

​de_app_answers

​Authentication Tables

​de_users

​de_usermeta

​Indexes and Performance

​Primary Indexes

​Foreign Key Indexes

​Recommended Additional Indexes

​Data Integrity

​Foreign Key Constraints

​Data Validation

​Schema Migration

​Sample Queries

​Get User Participation Summary

​Game Performance by Area

​Find Users with Failed Questions

​Database Initialization

​Next Steps

Build docs developers (and LLMs) love

Overview

Entity-Relationship Diagram

Core Tables

de_app_clients

de_app_areas

de_app_users

de_app_games

de_app_game_questions

de_app_sessions

de_app_answers

Authentication Tables

de_users

de_usermeta

Indexes and Performance

Primary Indexes

Foreign Key Indexes

Recommended Additional Indexes

Data Integrity

Foreign Key Constraints

Data Validation

Schema Migration

Sample Queries

Get User Participation Summary

Game Performance by Area

Find Users with Failed Questions

Database Initialization

Next Steps