diff options
Diffstat (limited to 'html/app/models/_info.md')
| -rw-r--r-- | html/app/models/_info.md | 214 |
1 files changed, 140 insertions, 74 deletions
diff --git a/html/app/models/_info.md b/html/app/models/_info.md index b5b2e1c..5504523 100644 --- a/html/app/models/_info.md +++ b/html/app/models/_info.md @@ -6,79 +6,145 @@ The former project includes 209 tables with 5 or more rows (and 325 tables total -## Partitioning the database schema +## 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; + +``` -Models shall organized in various *thematic entity-groups* in order to handle -the numerous relationd between tables. -* market ~10 - - [Ok] - products = cms_eStore_Products - - [Ok] - cms_Relationship_SimpleProducts_ProductCategories - - [Ok] - product_categries = cms_eStore_ProductCategories - - [Ok] - cms_eStore_PriceLists - - [Ok] - cms_eStore_Prices, - - [Ok] - products_to_images (assets) - - [Ok] - assets (*cms) - - [Ok] -brands = cms_eStore_Brands - -* sales ~ 15 - - orders = cms_eStore_Orders - - orderItems = cms_eStore_OrderItems - - payments - - refunds - - tracking - - orderEvents = cms_eStore_OrderEventLogs - - cms_eStore_OrderStatusDescriptions, - -* care - - [data] - customers = cms_YodaAddon_Customers - - [data] - addresses = cms_YodaAddon_Addresses - - [data] - userlists = cms_YodaAddon_UserLists - -* system ~ 5 - - security - - operators - - sessions * cms_UserSessions - - tokens - -* cms ~20 - - [?Ok] - about = cms_Pages - - stores = cms_YodaAddon_Stores - - cms_Relationship_Stores_Services - - cms_Relationship_Stores_Images - - cms_Relationship_Stores_Services - - news - - contests - - [Ok] - assets - - snippets, translations, whatever... - - -Then there shall be connecting-models betweem various entities, like: - -* Marketing: combines [market + cms] - -* Buing: combines [market + sales + care] - - - -## uncategorized: - -* cms_YodaAddon_Companies - - - - -NOTE: - -Also need to partition the "working-on/preccessing data" - -- CMS : content creation, design, etc - -- Orders : pick, select, track, manare returns, etc - -- Customers: navigate on store, fill basket, order, pay - -- Prices: ERP - -I suggest each group of proccessing is make by different application. -These applications may *speak* to eachother via API |