CREATE DATABASE workshopcf;
CREATE TABLE IF NOT EXISTS user_types
(
id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
assing_task BOOLEAN DEFAULT false
);
CREATE TABLE IF NOT EXISTS users
(
id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
user_type_id INT UNSIGNED NOT NULL,
FOREIGN KEY (user_type_id) REFERENCES user_types(id)
);
CREATE TABLE IF NOT EXISTS tasks
(
id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(50) NOT NULL,
description TEXT,
status ENUM(‘Pendiente’,’Proceso’,’Terminado’) DEFAULT ‘Proceso’,
deadline DATETIME,
user_id INT UNSIGNED NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id)
);
CREATE TABLE IF NOT EXISTS categories
(
id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL
);
CREATE TABLE IF NOT EXISTS task_categories
(
id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
task_id INT UNSIGNED NOT NULL,
category_id INT UNSIGNED NOT NULL,
FOREIGN KEY (task_id) REFERENCES tasks(id),
FOREIGN KEY (category_id) REFERENCES categories(id)
);