# info about the structrure of the models

According to the former impementation (atcom) the project will need to handle 200+ tables.
The former project includes 209 tables with 5 or more rows (and 325 tables totaly)



## Database schema

Main tables and recomended constraints:

```
SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

SET NAMES utf8mb4;

DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) NOT NULL COMMENT 'if 0 then this is a root course',
  `label` varchar(140) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `lesson`;
CREATE TABLE `lesson` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `course_id` int(11) NOT NULL,
  `title` varchar(140) COLLATE utf8mb4_unicode_ci NOT NULL,
  `body` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `published` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0 = unpublished, 1 = published',
  PRIMARY KEY (`id`),
  KEY `course_id` (`course_id`),
  CONSTRAINT `lesson_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `course` (`id`),
  CONSTRAINT `lesson_ibfk_2` FOREIGN KEY (`course_id`) REFERENCES `course` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `lesson_media`;
CREATE TABLE `lesson_media` (
  `lesson_id` int(11) NOT NULL,
  `media_id` int(11) NOT NULL,
  PRIMARY KEY (`lesson_id`,`media_id`),
  KEY `media_id` (`media_id`),
  CONSTRAINT `lesson_media_ibfk_1` FOREIGN KEY (`lesson_id`) REFERENCES `lesson` (`id`),
  CONSTRAINT `lesson_media_ibfk_2` FOREIGN KEY (`lesson_id`) REFERENCES `lesson` (`id`),
  CONSTRAINT `lesson_media_ibfk_3` FOREIGN KEY (`lesson_id`) REFERENCES `lesson` (`id`),
  CONSTRAINT `lesson_media_ibfk_4` FOREIGN KEY (`media_id`) REFERENCES `media` (`id`),
  CONSTRAINT `lesson_media_ibfk_5` FOREIGN KEY (`lesson_id`) REFERENCES `lesson` (`id`) ON DELETE NO ACTION,
  CONSTRAINT `lesson_media_ibfk_6` FOREIGN KEY (`media_id`) REFERENCES `media` (`id`) ON DELETE NO ACTION,
  CONSTRAINT `lesson_media_ibfk_7` FOREIGN KEY (`lesson_id`) REFERENCES `lesson` (`id`) ON DELETE NO ACTION,
  CONSTRAINT `lesson_media_ibfk_8` FOREIGN KEY (`media_id`) REFERENCES `media` (`id`) ON DELETE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `lesson_privilege`;
CREATE TABLE `lesson_privilege` (
  `lesson_id` int(11) NOT NULL,
  `privilege_id` int(11) NOT NULL COMMENT 'minimum privilege required to access the lesson',
  KEY `lesson_id` (`lesson_id`),
  KEY `privilege_id` (`privilege_id`),
  CONSTRAINT `lesson_privilege_ibfk_1` FOREIGN KEY (`lesson_id`) REFERENCES `lesson` (`id`),
  CONSTRAINT `lesson_privilege_ibfk_2` FOREIGN KEY (`privilege_id`) REFERENCES `privilege` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `media`;
CREATE TABLE `media` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `label` varchar(140) COLLATE utf8mb4_unicode_ci NOT NULL,
  `type` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `path` varchar(320) COLLATE utf8mb4_unicode_ci NOT NULL,
  `referable` tinyint(4) NOT NULL DEFAULT '1' COMMENT '0 = hidden, 1 = referable',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `page`;
CREATE TABLE `page` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(140) COLLATE utf8mb4_unicode_ci NOT NULL,
  `body` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `published` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0 = unpublished; 1 = published',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `page_media`;
CREATE TABLE `page_media` (
  `page_id` int(11) NOT NULL,
  `media_id` int(11) NOT NULL,
  KEY `page_id` (`page_id`),
  KEY `media_id` (`media_id`),
  CONSTRAINT `page_media_ibfk_1` FOREIGN KEY (`page_id`) REFERENCES `page` (`id`) ON DELETE NO ACTION,
  CONSTRAINT `page_media_ibfk_2` FOREIGN KEY (`media_id`) REFERENCES `media` (`id`) ON DELETE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `privilege`;
CREATE TABLE `privilege` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `alias` varchar(8) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'keep it simple; use latin',
  `label` varchar(140) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `alias` (`alias`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `privilege_inherit`;
CREATE TABLE `privilege_inherit` (
  `higher_id` int(11) NOT NULL COMMENT 'higher priviledges inherit (include) lower ones',
  `lower_id` int(11) NOT NULL,
  PRIMARY KEY (`higher_id`,`lower_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` int(11) NOT NULL,
  `last_name` int(11) NOT NULL,
  `email` int(11) NOT NULL,
  `expiration` int(11) NOT NULL COMMENT 'account expiration date; 0 = never',
  `password` int(11) NOT NULL,
  `salt` int(11) NOT NULL,
  `otp` int(11) NOT NULL COMMENT 'one time password for reset password',
  `otp_expiration` int(11) NOT NULL,
  `active` int(11) NOT NULL COMMENT 'account flag; 1=active, 0=inactive',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `user_privilege`;
CREATE TABLE `user_privilege` (
  `user_id` int(11) NOT NULL,
  `privilege_id` int(11) NOT NULL,
  PRIMARY KEY (`user_id`,`privilege_id`),
  KEY `privilege_id` (`privilege_id`),
  CONSTRAINT `user_privilege_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`),
  CONSTRAINT `user_privilege_ibfk_2` FOREIGN KEY (`privilege_id`) REFERENCES `privilege` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

```