Skip to content

Instantly share code, notes, and snippets.

@phreakin

phreakin/employee_management_schema.sql

Created August 13, 2022 10:59
Show Gist options
  • Save phreakin/9e5ed059acd285c32300217325878ed9 to your computer and use it in GitHub Desktop.
Save phreakin/9e5ed059acd285c32300217325878ed9 to your computer and use it in GitHub Desktop.
Download ZIP
employee_management_schema.sql
Raw
employee_management_schema.sql
-- MySQL dump 10.13 Distrib 8.0.14, for Win64 (x86_64)
--
-- Host: 127.0.0.1 Database: organization
-- ------------------------------------------------------
-- Server version 8.0.14
/*!40101 SET @OLD_CHARACTER_SET_CLIENT = @@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS = @@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION = @@COLLATION_CONNECTION */;
SET NAMES utf8;
/*!40103 SET @OLD_TIME_ZONE = @@TIME_ZONE */;
/*!40103 SET TIME_ZONE = '+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS = @@UNIQUE_CHECKS, UNIQUE_CHECKS = 0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS = @@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS = 0 */;
/*!40101 SET @OLD_SQL_MODE = @@SQL_MODE, SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES = @@SQL_NOTES, SQL_NOTES = 0 */;
--
-- Table structure for table `employee`
--
DROP TABLE IF EXISTS `employee`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
SET character_set_client = utf8mb4;
CREATE TABLE `employee`
(
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`userId` bigint(20) NOT NULL,
`organizationId` bigint(20) NOT NULL,
`roleId` bigint(20) NOT NULL,
`createdBy` bigint(20) NOT NULL,
`updatedBy` bigint(20) NOT NULL,
`code` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
`status` smallint(6) NOT NULL DEFAULT '0',
`createdAt` datetime NOT NULL,
`updatedAt` datetime DEFAULT NULL,
`startsAt` datetime DEFAULT NULL,
`endsAt` datetime DEFAULT NULL,
`notes` text COLLATE utf8mb4_unicode_ci,
PRIMARY KEY (`id`),
KEY `idx_employee_user` (`userId`) /*!80000 INVISIBLE */,
KEY `idx_employee_creator` (`createdBy`),
KEY `idx_employee_modifier` (`updatedBy`),
KEY `idx_employee_role` (`roleId`),
KEY `idx_employee_organization` (`organizationId`),
CONSTRAINT `fk_employee_creator` FOREIGN KEY (`createdBy`) REFERENCES `user` (`id`),
CONSTRAINT `fk_employee_modifier` FOREIGN KEY (`updatedBy`) REFERENCES `user` (`id`),
CONSTRAINT `fk_employee_organization` FOREIGN KEY (`organizationId`) REFERENCES `organization` (`id`),
CONSTRAINT `fk_employee_role` FOREIGN KEY (`roleId`) REFERENCES `role` (`id`),
CONSTRAINT `fk_employee_user` FOREIGN KEY (`userId`) REFERENCES `user` (`id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `employee`
--
LOCK TABLES `employee` WRITE;
/*!40000 ALTER TABLE `employee`
DISABLE KEYS */;
/*!40000 ALTER TABLE `employee`
ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `organization`
--
DROP TABLE IF EXISTS `organization`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
SET character_set_client = utf8mb4;
CREATE TABLE `organization`
(
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`createdBy` bigint(20) NOT NULL,
`updatedBy` bigint(20) NOT NULL,
`title` varchar(75) COLLATE utf8mb4_unicode_ci NOT NULL,
`metaTitle` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`slug` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
`summary` tinytext COLLATE utf8mb4_unicode_ci,
`status` smallint(6) NOT NULL DEFAULT '0',
`createdAt` datetime NOT NULL,
`updatedAt` datetime DEFAULT NULL,
`profile` text COLLATE utf8mb4_unicode_ci,
`content` text COLLATE utf8mb4_unicode_ci,
PRIMARY KEY (`id`),
UNIQUE KEY `uq_slug` (`slug`),
KEY `idx_organization_creator` (`createdBy`),
KEY `idx_organization_modifier` (`updatedBy`),
CONSTRAINT `fk_organization_creator` FOREIGN KEY (`createdBy`) REFERENCES `user` (`id`),
CONSTRAINT `fk_organization_modifier` FOREIGN KEY (`updatedBy`) REFERENCES `user` (`id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `organization`
--
LOCK TABLES `organization` WRITE;
/*!40000 ALTER TABLE `organization`
DISABLE KEYS */;
/*!40000 ALTER TABLE `organization`
ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `organization_meta`
--
DROP TABLE IF EXISTS `organization_meta`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
SET character_set_client = utf8mb4;
CREATE TABLE `organization_meta`
(
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`organizationId` bigint(20) NOT NULL,
`key` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
`content` text COLLATE utf8mb4_unicode_ci,
PRIMARY KEY (`id`),
UNIQUE KEY `uq_meta_organization` (`organizationId`, `key`),
KEY `idx_meta_organization` (`organizationId`),
CONSTRAINT `fk_meta_organization` FOREIGN KEY (`organizationId`) REFERENCES `organization` (`id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `organization_meta`
--
LOCK TABLES `organization_meta` WRITE;
/*!40000 ALTER TABLE `organization_meta`
DISABLE KEYS */;
/*!40000 ALTER TABLE `organization_meta`
ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `permission`
--
DROP TABLE IF EXISTS `permission`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
SET character_set_client = utf8mb4;
CREATE TABLE `permission`
(
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`title` varchar(75) COLLATE utf8mb4_unicode_ci NOT NULL,
`slug` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
`description` tinytext COLLATE utf8mb4_unicode_ci,
`type` smallint(6) NOT NULL DEFAULT '0',
`active` tinyint(4) NOT NULL DEFAULT '0',
`createdAt` datetime NOT NULL,
`updatedAt` datetime DEFAULT NULL,
`content` text COLLATE utf8mb4_unicode_ci,
PRIMARY KEY (`id`),
UNIQUE KEY `uq_slug` (`slug`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `permission`
--
LOCK TABLES `permission` WRITE;
/*!40000 ALTER TABLE `permission`
DISABLE KEYS */;
/*!40000 ALTER TABLE `permission`
ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `role`
--
DROP TABLE IF EXISTS `role`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
SET character_set_client = utf8mb4;
CREATE TABLE `role`
(
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`title` varchar(75) COLLATE utf8mb4_unicode_ci NOT NULL,
`slug` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
`description` tinytext COLLATE utf8mb4_unicode_ci,
`type` smallint(6) NOT NULL DEFAULT '0',
`active` tinyint(4) NOT NULL DEFAULT '0',
`createdAt` datetime NOT NULL,
`updatedAt` datetime DEFAULT NULL,
`content` text COLLATE utf8mb4_unicode_ci,
PRIMARY KEY (`id`),
UNIQUE KEY `uq_slug` (`slug`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `role`
--
LOCK TABLES `role` WRITE;
/*!40000 ALTER TABLE `role`
DISABLE KEYS */;
/*!40000 ALTER TABLE `role`
ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `role_permission`
--
DROP TABLE IF EXISTS `role_permission`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
SET character_set_client = utf8mb4;
CREATE TABLE `role_permission`
(
`roleId` bigint(20) NOT NULL,
`permissionId` bigint(20) NOT NULL,
`createdAt` datetime NOT NULL,
`updatedAt` datetime DEFAULT NULL,
PRIMARY KEY (`roleId`, `permissionId`),
KEY `idx_rp_role` (`roleId`),
KEY `idx_rp_permission` (`permissionId`),
CONSTRAINT `fk_rp_permission` FOREIGN KEY (`permissionId`) REFERENCES `permission` (`id`),
CONSTRAINT `fk_rp_role` FOREIGN KEY (`roleId`) REFERENCES `role` (`id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `role_permission`
--
LOCK TABLES `role_permission` WRITE;
/*!40000 ALTER TABLE `role_permission`
DISABLE KEYS */;
/*!40000 ALTER TABLE `role_permission`
ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `user`
--
DROP TABLE IF EXISTS `user`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
SET character_set_client = utf8mb4;
CREATE TABLE `user`
(
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`roleId` bigint(20) NOT NULL,
`firstName` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`middleName` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`lastName` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`username` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`mobile` varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`email` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`passwordHash` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
`registeredAt` datetime NOT NULL,
`lastLogin` datetime DEFAULT NULL,
`intro` tinytext COLLATE utf8mb4_unicode_ci,
`profile` text COLLATE utf8mb4_unicode_ci,
PRIMARY KEY (`id`),
UNIQUE KEY `uq_username` (`username`),
UNIQUE KEY `uq_mobile` (`mobile`),
UNIQUE KEY `uq_email` (`email`),
KEY `idx_user_role` (`roleId`),
CONSTRAINT `fk_user_role` FOREIGN KEY (`roleId`) REFERENCES `role` (`id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `user`
--
LOCK TABLES `user` WRITE;
/*!40000 ALTER TABLE `user`
DISABLE KEYS */;
/*!40000 ALTER TABLE `user`
ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE = @OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE = @OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS = @OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS = @OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT = @OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS = @OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION = @OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES = @OLD_SQL_NOTES */;
-- Dump completed on 2020-11-01 13:33:42
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment