diff options
Diffstat (limited to 'public/app/models/_info.md')
| -rw-r--r-- | public/app/models/_info.md | 150 |
1 files changed, 150 insertions, 0 deletions
diff --git a/public/app/models/_info.md b/public/app/models/_info.md new file mode 100644 index 0000000..5504523 --- /dev/null +++ b/public/app/models/_info.md @@ -0,0 +1,150 @@ + +# 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; + +``` + + |