blog_educativo-bd

 

Sí. Para esta primera etapa no intentaría construir todavía todo el sistema. Lo mejor es hacer un CMS pequeño que nos permita comprobar que la estructura de la base funciona correctamente.

Yo cambiaría un detalle de nomenclatura: en lugar de contenidos, utilizaría entradas para cada publicación del blog. Cada entrada tendrá:

  • Tema
  • Subtema
  • Título
  • Subtítulo
  • Contenido enriquecido
  • Imagen/video asociados posteriormente
  • Estado
  • Fecha

Así después podremos reutilizar esas entradas para las actividades educativas.

1. Modelo que te recomiendo

La parte principal quedaría así:

TEMAS
  │
  └── SUBTEMAS
         │
         └── ENTRADAS
                │
                └── MULTIMEDIA
                       ├── Imágenes
                       └── Videos


TEMAS
  │
  ├── PREGUNTAS
  │      └── RESPUESTAS
  │
  ├── FLASHCARDS
  │
  └── EXÁMENES
          └── EXAMEN_PREGUNTAS

Y algo muy importante:

Memorama no necesita inicialmente una tabla propia. Puede tomar imágenes de multimedia y generar las parejas dinámicamente.

2. Base de datos database.sql

Esta sería mi primera versión de la base de datos.

CREATE DATABASE IF NOT EXISTS blog_educativo
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

USE blog_educativo;

SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS respuestas_examen;
DROP TABLE IF EXISTS intentos_examen;
DROP TABLE IF EXISTS examen_preguntas;
DROP TABLE IF EXISTS examenes;
DROP TABLE IF EXISTS flashcards;
DROP TABLE IF EXISTS respuestas;
DROP TABLE IF EXISTS preguntas;
DROP TABLE IF EXISTS multimedia;
DROP TABLE IF EXISTS entradas;
DROP TABLE IF EXISTS subtemas;
DROP TABLE IF EXISTS temas;
DROP TABLE IF EXISTS usuarios;

SET FOREIGN_KEY_CHECKS = 1;


-- =========================================================
-- USUARIOS
-- =========================================================

CREATE TABLE usuarios (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(150) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    rol ENUM('admin', 'profesor', 'alumno') NOT NULL DEFAULT 'admin',
    activo TINYINT(1) NOT NULL DEFAULT 1,
    fecha_creacion DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    fecha_actualizacion DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;


-- =========================================================
-- TEMAS
-- =========================================================

CREATE TABLE temas (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(150) NOT NULL,
    slug VARCHAR(180) NOT NULL UNIQUE,
    descripcion TEXT NULL,
    activo TINYINT(1) NOT NULL DEFAULT 1,
    fecha_creacion DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    fecha_actualizacion DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_temas_activo (activo)
) ENGINE=InnoDB;


-- =========================================================
-- SUBTEMAS
-- =========================================================

CREATE TABLE subtemas (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tema_id INT UNSIGNED NOT NULL,
    nombre VARCHAR(150) NOT NULL,
    slug VARCHAR(180) NOT NULL,
    descripcion TEXT NULL,
    activo TINYINT(1) NOT NULL DEFAULT 1,
    fecha_creacion DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    fecha_actualizacion DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_subtemas_tema
        FOREIGN KEY (tema_id)
        REFERENCES temas(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

    UNIQUE KEY uk_subtema_tema_slug (tema_id, slug),
    INDEX idx_subtemas_tema (tema_id),
    INDEX idx_subtemas_activo (activo)
) ENGINE=InnoDB;


-- =========================================================
-- ENTRADAS DEL BLOG
-- =========================================================

CREATE TABLE entradas (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    tema_id INT UNSIGNED NOT NULL,
    subtema_id INT UNSIGNED NULL,

    titulo VARCHAR(250) NOT NULL,
    subtitulo VARCHAR(500) NULL,

    slug VARCHAR(280) NOT NULL UNIQUE,

    resumen TEXT NULL,

    contenido LONGTEXT NOT NULL,

    estado ENUM('borrador', 'publicado', 'archivado')
        NOT NULL DEFAULT 'borrador',

    orden INT NOT NULL DEFAULT 0,

    fecha_publicacion DATETIME NULL,

    fecha_creacion DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    fecha_actualizacion DATETIME NULL
        DEFAULT NULL
        ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_entradas_tema
        FOREIGN KEY (tema_id)
        REFERENCES temas(id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,

    CONSTRAINT fk_entradas_subtema
        FOREIGN KEY (subtema_id)
        REFERENCES subtemas(id)
        ON DELETE SET NULL
        ON UPDATE CASCADE,

    INDEX idx_entradas_tema (tema_id),
    INDEX idx_entradas_subtema (subtema_id),
    INDEX idx_entradas_estado (estado),
    INDEX idx_entradas_publicacion (fecha_publicacion)
) ENGINE=InnoDB;


-- =========================================================
-- MULTIMEDIA
-- =========================================================

CREATE TABLE multimedia (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    tema_id INT UNSIGNED NULL,
    subtema_id INT UNSIGNED NULL,
    entrada_id INT UNSIGNED NULL,

    tipo ENUM('imagen', 'video') NOT NULL,

    nombre_original VARCHAR(255) NOT NULL,
    nombre_archivo VARCHAR(255) NOT NULL,
    ruta VARCHAR(500) NOT NULL,

    extension VARCHAR(20) NULL,
    mime_type VARCHAR(100) NULL,
    tamano BIGINT UNSIGNED NULL,

    titulo VARCHAR(250) NULL,
    descripcion TEXT NULL,
    alt_text VARCHAR(250) NULL,

    orden INT NOT NULL DEFAULT 0,

    activo TINYINT(1) NOT NULL DEFAULT 1,

    fecha_creacion DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_multimedia_tema
        FOREIGN KEY (tema_id)
        REFERENCES temas(id)
        ON DELETE SET NULL
        ON UPDATE CASCADE,

    CONSTRAINT fk_multimedia_subtema
        FOREIGN KEY (subtema_id)
        REFERENCES subtemas(id)
        ON DELETE SET NULL
        ON UPDATE CASCADE,

    CONSTRAINT fk_multimedia_entrada
        FOREIGN KEY (entrada_id)
        REFERENCES entradas(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

    INDEX idx_multimedia_tema (tema_id),
    INDEX idx_multimedia_subtema (subtema_id),
    INDEX idx_multimedia_entrada (entrada_id),
    INDEX idx_multimedia_tipo (tipo)
) ENGINE=InnoDB;


-- =========================================================
-- PREGUNTAS
-- =========================================================

CREATE TABLE preguntas (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    tema_id INT UNSIGNED NOT NULL,
    subtema_id INT UNSIGNED NULL,

    multimedia_id INT UNSIGNED NULL,

    tipo ENUM(
        'multiple_choice',
        'visual'
    ) NOT NULL DEFAULT 'multiple_choice',

    pregunta TEXT NOT NULL,

    explicacion TEXT NULL,

    dificultad ENUM(
        'facil',
        'media',
        'dificil'
    ) NOT NULL DEFAULT 'media',

    activo TINYINT(1) NOT NULL DEFAULT 1,

    fecha_creacion DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    fecha_actualizacion DATETIME NULL
        DEFAULT NULL
        ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_preguntas_tema
        FOREIGN KEY (tema_id)
        REFERENCES temas(id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,

    CONSTRAINT fk_preguntas_subtema
        FOREIGN KEY (subtema_id)
        REFERENCES subtemas(id)
        ON DELETE SET NULL
        ON UPDATE CASCADE,

    CONSTRAINT fk_preguntas_multimedia
        FOREIGN KEY (multimedia_id)
        REFERENCES multimedia(id)
        ON DELETE SET NULL
        ON UPDATE CASCADE,

    INDEX idx_preguntas_tema (tema_id),
    INDEX idx_preguntas_subtema (subtema_id),
    INDEX idx_preguntas_tipo (tipo)
) ENGINE=InnoDB;


-- =========================================================
-- RESPUESTAS
-- =========================================================

CREATE TABLE respuestas (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    pregunta_id INT UNSIGNED NOT NULL,

    respuesta TEXT NOT NULL,

    es_correcta TINYINT(1) NOT NULL DEFAULT 0,

    orden INT NOT NULL DEFAULT 0,

    activo TINYINT(1) NOT NULL DEFAULT 1,

    fecha_creacion DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_respuestas_pregunta
        FOREIGN KEY (pregunta_id)
        REFERENCES preguntas(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

    INDEX idx_respuestas_pregunta (pregunta_id),
    INDEX idx_respuestas_correcta (pregunta_id, es_correcta)
) ENGINE=InnoDB;


-- =========================================================
-- FLASHCARDS
-- =========================================================

CREATE TABLE flashcards (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    tema_id INT UNSIGNED NOT NULL,
    subtema_id INT UNSIGNED NULL,

    multimedia_id INT UNSIGNED NULL,

    frente TEXT NOT NULL,
    reverso TEXT NOT NULL,

    orden INT NOT NULL DEFAULT 0,

    activo TINYINT(1) NOT NULL DEFAULT 1,

    fecha_creacion DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    fecha_actualizacion DATETIME NULL
        DEFAULT NULL
        ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_flashcards_tema
        FOREIGN KEY (tema_id)
        REFERENCES temas(id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,

    CONSTRAINT fk_flashcards_subtema
        FOREIGN KEY (subtema_id)
        REFERENCES subtemas(id)
        ON DELETE SET NULL
        ON UPDATE CASCADE,

    CONSTRAINT fk_flashcards_multimedia
        FOREIGN KEY (multimedia_id)
        REFERENCES multimedia(id)
        ON DELETE SET NULL
        ON UPDATE CASCADE,

    INDEX idx_flashcards_tema (tema_id),
    INDEX idx_flashcards_subtema (subtema_id)
) ENGINE=InnoDB;


-- =========================================================
-- EXAMENES
-- =========================================================

CREATE TABLE examenes (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    titulo VARCHAR(250) NOT NULL,
    descripcion TEXT NULL,

    tema_id INT UNSIGNED NOT NULL,
    subtema_id INT UNSIGNED NULL,

    numero_preguntas INT UNSIGNED NOT NULL DEFAULT 10,
    numero_opciones INT UNSIGNED NOT NULL DEFAULT 4,

    activo TINYINT(1) NOT NULL DEFAULT 1,

    fecha_creacion DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    fecha_actualizacion DATETIME NULL
        DEFAULT NULL
        ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_examenes_tema
        FOREIGN KEY (tema_id)
        REFERENCES temas(id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,

    CONSTRAINT fk_examenes_subtema
        FOREIGN KEY (subtema_id)
        REFERENCES subtemas(id)
        ON DELETE SET NULL
        ON UPDATE CASCADE,

    INDEX idx_examenes_tema (tema_id),
    INDEX idx_examenes_subtema (subtema_id)
) ENGINE=InnoDB;


-- =========================================================
-- PREGUNTAS DE EXAMEN
-- =========================================================

CREATE TABLE examen_preguntas (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    examen_id INT UNSIGNED NOT NULL,
    pregunta_id INT UNSIGNED NOT NULL,

    orden INT NOT NULL DEFAULT 0,

    CONSTRAINT fk_examen_preguntas_examen
        FOREIGN KEY (examen_id)
        REFERENCES examenes(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

    CONSTRAINT fk_examen_preguntas_pregunta
        FOREIGN KEY (pregunta_id)
        REFERENCES preguntas(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

    UNIQUE KEY uk_examen_pregunta (examen_id, pregunta_id),

    INDEX idx_examen_preguntas_examen (examen_id),
    INDEX idx_examen_preguntas_pregunta (pregunta_id)
) ENGINE=InnoDB;


-- =========================================================
-- INTENTOS DE EXAMEN
-- =========================================================

CREATE TABLE intentos_examen (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    examen_id INT UNSIGNED NOT NULL,
    usuario_id INT UNSIGNED NULL,

    fecha_inicio DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    fecha_fin DATETIME NULL,

    calificacion DECIMAL(5,2) NULL,

    CONSTRAINT fk_intentos_examen
        FOREIGN KEY (examen_id)
        REFERENCES examenes(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

    CONSTRAINT fk_intentos_usuario
        FOREIGN KEY (usuario_id)
        REFERENCES usuarios(id)
        ON DELETE SET NULL
        ON UPDATE CASCADE,

    INDEX idx_intentos_examen (examen_id),
    INDEX idx_intentos_usuario (usuario_id)
) ENGINE=InnoDB;


-- =========================================================
-- RESPUESTAS DEL EXAMEN
-- =========================================================

CREATE TABLE respuestas_examen (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    intento_id INT UNSIGNED NOT NULL,
    pregunta_id INT UNSIGNED NOT NULL,
    respuesta_id INT UNSIGNED NULL,

    es_correcta TINYINT(1) NOT NULL DEFAULT 0,

    CONSTRAINT fk_respuestas_examen_intento
        FOREIGN KEY (intento_id)
        REFERENCES intentos_examen(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

    CONSTRAINT fk_respuestas_examen_pregunta
        FOREIGN KEY (pregunta_id)
        REFERENCES preguntas(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

    CONSTRAINT fk_respuestas_examen_respuesta
        FOREIGN KEY (respuesta_id)
        REFERENCES respuestas(id)
        ON DELETE SET NULL
        ON UPDATE CASCADE,

    INDEX idx_respuestas_examen_intento (intento_id),
    INDEX idx_respuestas_examen_pregunta (pregunta_id)
) ENGINE=InnoDB;


-- =========================================================
-- DATOS INICIALES
-- =========================================================

INSERT INTO temas
(nombre, slug, descripcion)
VALUES
(
    'Matemáticas',
    'matematicas',
    'Contenido educativo de matemáticas'
),
(
    'Español',
    'espanol',
    'Contenido educativo de español'
),
(
    'Inglés',
    'ingles',
    'Contenido educativo de inglés'
);


INSERT INTO subtemas
(tema_id, nombre, slug, descripcion)
VALUES
(
    1,
    'Fracciones',
    'fracciones',
    'Conceptos básicos sobre fracciones'
),
(
    1,
    'Multiplicaciones',
    'multiplicaciones',
    'Multiplicaciones y ejercicios'
),
(
    2,
    'Gramática',
    'gramatica',
    'Conceptos básicos de gramática'
),
(
    3,
    'Vocabulario',
    'vocabulario',
    'Vocabulario básico en inglés'
);


-- =========================================================
-- USUARIO ADMINISTRADOR DE PRUEBA
-- =========================================================
-- La contraseña deberá generarse con password_hash()
-- desde PHP. No colocar contraseñas reales aquí.

Una decisión importante sobre entradas

Yo sí pondría titulo, subtitulo y contenido directamente en entradas.

No haría:

entrada
   |
   +-- titulo
   +-- subtitulo
   +-- contenido

en tres tablas diferentes.

Una entrada es una unidad lógica y esos tres elementos pertenecen a ella.

Por ejemplo:

Tema: Matemáticas
Subtema: Fracciones

Título:
¿Qué son las fracciones?

Subtítulo:
Conceptos básicos para comprender las fracciones

Contenido:
[contenido creado con Trumbowyg]

Eso te va a resultar mucho más sencillo de administrar.

3. Sobre Trumbowyg

Para este prototipo inicial, me parece una muy buena elección.

Aunque en el proyecto final podemos mantener el frontend sin Bootstrap, para esta primera prueba podemos usar:

  • Bootstrap → solamente para que el CMS se vea ordenado.
  • jQuery → requerido por Trumbowyg.
  • Trumbowyg → editor de contenido.

Después podemos quitar Bootstrap si quieres mantener absolutamente todo minimalista.

Tu editor podría quedar prácticamente como lo planteaste:

$('#editor').trumbowyg({
    lang: 'es',
    btns: [
        ['viewHTML'],
        ['formatting'],
        ['bold', 'italic', 'underline'],
        ['superscript', 'subscript'],
        ['link'],
        ['insertImage'],
        ['unorderedList', 'orderedList'],
        ['justifyLeft', 'justifyCenter', 'justifyRight', 'justifyFull'],
        ['horizontalRule'],
        ['removeformat'],
        ['fullscreen']
    ]
});

4. Ahora hagamos un CMS mínimo

Para probar la base de datos no necesitamos todavía todo el proyecto.

Hagamos solamente:

/blog
│
├── config/
│   └── database.php
│
├── admin/
│   ├── index.php
│   ├── nueva-entrada.php
│   └── guardar-entrada.php
│
├── entrada.php
│
└── index.php

Con esto podremos comprobar:

  1. Conexión a MySQL.
  2. Temas.
  3. Subtemas.
  4. Entradas.
  5. Título.
  6. Subtítulo.
  7. Contenido HTML de Trumbowyg.
  8. Guardado.
  9. Listado.
  10. Visualización.

5. Conexión PDO

config/database.php

<?php

$host = 'localhost';
$dbname = 'blog_educativo';
$user = 'root';
$password = '';

try {

    $pdo = new PDO(
        "mysql:host=$host;dbname=$dbname;charset=utf8mb4",
        $user,
        $password,
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false
        ]
    );

} catch (PDOException $e) {

    die('Error de conexión: ' . $e->getMessage());

}

Comentarios

Publicar un comentario

Entradas más populares de este blog

PROMPT MAESTRO — PLATAFORMA EDUCATIVA PHP + MYSQL

  Mi recomendación Con este prompt ya tienes una especificación bastante seria del proyecto , no solamente un prompt para "hacer un blog". Lo más importante que quedó resuelto es esto: Una imagen → múltiples usos. Una imagen puede estar en una lección, Flashcard, Quiz Visual y Memorama sin duplicarla. Una pregunta → múltiples usos. Una pregunta puede aparecer en un examen, Quiz Visual o actividad de repaso. Una respuesta → banco reutilizable. El sistema puede tomar la correcta y seleccionar aleatoriamente las incorrectas. Un tema → múltiples actividades. Por ejemplo, Matemáticas → Fracciones puede tener contenido + Flashcards + Quiz Visual + Examen + Memorama. Y, sobre todo, no estamos construyendo la IA ahora , pero estamos evitando decisiones de base de datos que después hagan difícil incorporarla. El siguiente paso ideal sería convertir este prompt en el diseño técnico real de la base de datos , incluyendo el database.sql completo y un diagrama de relaciones ...
Leer más

bd-2

 CREATE DATABASE IF NOT EXISTS blog_educativo CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE blog_educativo; SET FOREIGN_KEY_CHECKS = 0; DROP TABLE IF EXISTS respuestas_examen; DROP TABLE IF EXISTS intentos_examen; DROP TABLE IF EXISTS examen_preguntas; DROP TABLE IF EXISTS examenes; DROP TABLE IF EXISTS flashcards; DROP TABLE IF EXISTS respuestas; DROP TABLE IF EXISTS preguntas; DROP TABLE IF EXISTS multimedia; DROP TABLE IF EXISTS entradas; DROP TABLE IF EXISTS subtemas; DROP TABLE IF EXISTS temas; SET FOREIGN_KEY_CHECKS = 1; -- ========================================================= -- TEMAS -- ========================================================= CREATE TABLE temas (     id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,     nombre VARCHAR(150) NOT NULL,     slug VARCHAR(180) NOT NULL UNIQUE,     descripcion TEXT NULL,     activo TINYINT(1) NOT NULL DEFAULT 1,     fecha_creacion DATETIME NOT NULL DEFAULT CURRENT_...
Leer más

La Estructura que Simula una Base de Datos en PHP

  Arrays Asociativos: La Estructura que Simula una Base de Datos en PHP ¡Excelente pregunta! Efectivamente,  los arrays asociativos son los que más se parecen a una base de datos  porque permiten una estructura organizada con claves y valores, similar a una tabla. 🏗️ Simulando una Tabla de Base de Datos php <?php // Estructura que simula una tabla de usuarios en una BD $usuarios = [ [ "id" => 1 , "nombre" => "Ana García" , "email" => "ana@email.com" , "edad" => 28 , "activo" => true , "fecha_registro" => "2024-01-15" ] , [ "id" => 2 , "nombre" => "Carlos López" , "email" => "carlos@email.com" , "edad" => 35 , "activo" => false , "fecha_regis...
Leer más