Modelo de Base de datos de un sistema de preguntas y respuestas

El siguiente modelo de base de datos fue sacado del sistema QAX: sistema de preguntas y respuestas , espero les sirva.

 El sistema de preguntas y respuestas esta basado en que un usuario hace una pregunta y los demas usuarios le responden, el usuario que realizo la pregunta inicial puede seleccionar como correcta una de las varias respuestas.

Este modelo de base de datos es parte de mi Directorio Maestro de Modelos de Bases de Datos.

Todos los usuarios pueden hacer votaciones a las preguntas y respuestas.

A continuación les explico las tablas y al final les agrego el código SQL completo.

1. Tabla: user (Usuarios)

Esta tabla centraliza la información de los miembros de la plataforma, gestionando su identidad y niveles de acceso.

• id: Identificador único (Primary Key) autoincremental.
• name / lastname: Nombre y apellido del usuario.
• username: Nombre de visualización único en la plataforma.
• email: Dirección de correo electrónico (utilizado para acceso y notificaciones).
• password: Hash de seguridad para la autenticación.
• image: Ruta o URL del archivo de imagen de perfil.
• is_active: Estado lógico del usuario (1 para usuarios vigentes, 0 para cuentas suspendidas).
• is_admin: Define el rol del usuario (1 para administradores con privilegios, 0 para usuarios regulares).
• created_at: Marca de tiempo de registro inicial.

2. Tabla: category (Categorías)

Funciona como un catálogo maestro para clasificar y filtrar las preguntas por temas de interés.

• id: Identificador único de la categoría.
• name: Etiqueta descriptiva del tema (ej. “Programación”, “Hardware”, “Diseño”).

3. Tabla: question (Preguntas)

Almacena las consultas realizadas por los usuarios, vinculándolas a un autor y una temática.

• id: Identificador único de la publicación.
• title: Resumen breve o título de la duda.
• description: Cuerpo detallado de la consulta (soporta texto extenso).
• tags: Palabras clave separadas por comas para optimizar la búsqueda.
• user_id: Referencia (Foreign Key) al autor de la pregunta.
• category_id: Referencia (Foreign Key) a la categoría temática.
• is_solved: Indicador de estado (se activa cuando el autor marca una respuesta como definitiva).
• created_at: Fecha y hora de publicación.

4. Tabla: answer (Respuestas)

Contiene las aportaciones o soluciones propuestas por la comunidad a una pregunta específica.

• id: Identificador único de la respuesta.
• description: Contenido técnico o explicativo de la solución.
• user_id: Referencia al usuario que aporta la solución.
• question_id: Referencia a la pregunta original a la que pertenece esta respuesta.
• is_correct: Booleano que destaca esta respuesta como la solución oficial o más útil.
• created_at: Fecha y hora de registro.

5. Tabla: vote (Votos/Valoraciones)

Gestiona el sistema de reputación, permitiendo calificar la utilidad tanto de preguntas como de respuestas.

• id: Identificador único del voto.
• kind_id: Discriminador de tipo (1 para votos en Preguntas, 2 para votos en Respuestas).
• ref_id: ID de referencia del registro votado (perteneciente a la tabla indicada en kind_id).
• val: Valor numérico del voto (típicamente +1 para apoyo y -1 para desacuerdo).
• user_id: Referencia al usuario que emite el voto (para evitar duplicidad de votos).
• created_at: Fecha y hora en que se realizó la acción.

Consultas utiles

Seleccionar todas las preguntas por el ID de categoria.

select * from question where category_id=X

x= id de la categoria

Seleccionar todas las respuestas de una pregunta

select * from answer whete question_id=X

X=id de la pregunta

Seleccionar todas las preguntas de un usuario.

select * from question where user_id=X

X = id del usuario

Sumar los votos de una pregunta

select sum(val) from vote where kind_id=1 and ref_id=Y

Y = id de una pregunta

Sumar votos de una respuesta

select sum(val) from vote where kind_id=2 and ref_id=Y

Y = id de una respuesta

Codigo SQL

A continuacion todo el SQL de las tablas.

-- 1. Creación de la base de datos y selección de contexto
CREATE DATABASE qax;
USE qax;

-- 2. Tabla de Usuarios: Almacena credenciales y perfil de los miembros
CREATE TABLE user(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    lastname VARCHAR(50) NOT NULL,
    username VARCHAR(50),
    email VARCHAR(255) NOT NULL,
    password VARCHAR(60) NOT NULL, -- Recomendado para hashes como BCrypt
    image VARCHAR(255) NOT NULL,   -- Ruta de la imagen de perfil
    is_active BOOLEAN NOT NULL DEFAULT 1, -- Estado de la cuenta (1=Activo, 0=Baneado/Inactivo)
    is_admin BOOLEAN NOT NULL DEFAULT 0,  -- Permisos de administrador
    created_at DATETIME NOT NULL
);

-- 3. Tabla de Categorías: Clasificación temática para las preguntas
CREATE TABLE category(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);

-- 4. Tabla de Preguntas: Contenido principal publicado por los usuarios
CREATE TABLE question(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    description TEXT,
    tags VARCHAR(255),             -- Etiquetas separadas por comas o espacios
    user_id INT NOT NULL,          -- Autor de la pregunta
    category_id INT,               -- Categoría a la que pertenece
    is_solved BOOLEAN NOT NULL DEFAULT 0, -- Indica si el autor ya marcó una respuesta como válida
    created_at DATETIME NOT NULL,
    FOREIGN KEY (user_id) REFERENCES user(id),
    FOREIGN KEY (category_id) REFERENCES category(id)
);

-- 5. Tabla de Respuestas: Soluciones o comentarios a una pregunta específica
CREATE TABLE answer(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    description TEXT,
    user_id INT NOT NULL,          -- Autor de la respuesta
    question_id INT,               -- Pregunta a la que responde
    is_correct BOOLEAN NOT NULL DEFAULT 0, -- Marcada como la mejor respuesta
    created_at DATETIME NOT NULL,
    FOREIGN KEY (user_id) REFERENCES user(id),
    FOREIGN KEY (question_id) REFERENCES question(id)
);

-- 6. Tabla de Votos: Sistema de reputación (Likes/Dislikes)
CREATE TABLE vote(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    kind_id INT,                   -- Tipo de entidad votada (1 = Pregunta, 2 = Respuesta)
    ref_id INT,                    -- ID de la pregunta o respuesta referenciada
    val INT NOT NULL DEFAULT 0,    -- Valor del voto (ej: 1 para positivo, -1 para negativo)
    created_at DATETIME NOT NULL,
    user_id INT NOT NULL,          -- Usuario que realiza el voto
    FOREIGN KEY (user_id) REFERENCES user(id)
);

Si les sirve este ejemplo no duden en compartirlo, en dejar sus comentarios o suscribirse al blog o a mi canal de youtube.