conference_room.sql 6.7 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202
  1. /*
  2. Navicat MySQL Data Transfer
  3. Source Server : localhost_3306
  4. Source Server Version : 50635
  5. Source Host : localhost:3306
  6. Source Database : conference_room
  7. Target Server Type : MYSQL
  8. Target Server Version : 50635
  9. File Encoding : 65001
  10. Date: 2019-02-12 22:28:13
  11. */
  12. SET FOREIGN_KEY_CHECKS=0;
  13. -- ----------------------------
  14. -- Table structure for reservation
  15. -- ----------------------------
  16. DROP TABLE IF EXISTS `reservation`;
  17. CREATE TABLE `reservation` (
  18. `id` int(11) NOT NULL AUTO_INCREMENT,
  19. `room_id` int(11) DEFAULT NULL,
  20. `user` varchar(32) DEFAULT NULL,
  21. `mobile` varchar(20) DEFAULT NULL,
  22. `date` date DEFAULT NULL,
  23. `begintime` time DEFAULT NULL,
  24. `endtime` time DEFAULT NULL,
  25. `mark` varchar(10) DEFAULT NULL,
  26. -- 主题
  27. `subject` varchar(60) DEFAULT NULL,
  28. -- 部门
  29. `department` varchar(60) DEFAULT NULL,
  30. -- 会议纪要
  31. `summary` varchar(5000) DEFAULT NULL,
  32. PRIMARY KEY (`id`),
  33. KEY `fk_reservation_room` (`room_id`),
  34. CONSTRAINT `fk_reservation_room` FOREIGN KEY (`room_id`) REFERENCES `room` (`id`)
  35. ) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8;
  36. -- ----------------------------
  37. -- Records of reservation
  38. -- ----------------------------
  39. -- ---------------------------------------------------
  40. -- Table structure for reservation_users--参会人员
  41. -- ---------------------------------------------------
  42. Drop TABLE IF EXISTS `reservation_users`;
  43. CREATE Table `reservation_users`(
  44. `id` int(11) NOT NULL AUTO_INCREMENT,
  45. `reservation_id` int(11) NOT NULL,
  46. `user_id` varchar(32) NOT NULL,
  47. PRIMARY KEY (`id`),
  48. KEY `fk_reservation_users_reservation` (reservation_id),
  49. CONSTRAINT `fk_reservation_users_reservation` FOREIGN KEY (`reservation_id`) REFERENCES `reservation` (`id`),
  50. KEY `fk_reservation_users_user` (user_id),
  51. CONSTRAINT `fk_reservation_users_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
  52. )ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8;
  53. -- ----------------------------
  54. -- Records of reservation_users
  55. -- ----------------------------
  56. -- ---------------------------------------------------
  57. -- Table structure for reservation_resource--会议资料
  58. -- ---------------------------------------------------
  59. Drop TABLE IF EXISTS `reservation_resource`;
  60. CREATE TABLE `reservation_resource` (
  61. `id` int(11) NOT NULL AUTO_INCREMENT,
  62. `reservation_id` int(11) NOT NULL,
  63. -- 资料名称
  64. `name` varchar(60) NOT NULL,
  65. -- 资料路径
  66. `path` varchar(100) NOT NULL,
  67. PRIMARY KEY (`id`),
  68. KEY `fk_reservation_users_room` (reservation_id),
  69. CONSTRAINT `fk_reservation_users_room` FOREIGN KEY (`reservation_id`) REFERENCES `reservation` (`id`)
  70. )ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8;
  71. -- ----------------------------
  72. -- Records of reservation_resource
  73. -- ----------------------------
  74. -- ----------------------------
  75. -- Table structure for role
  76. -- ----------------------------
  77. DROP TABLE IF EXISTS `role`;
  78. CREATE TABLE `role` (
  79. `role_id` int(11) NOT NULL,
  80. `role_name` varchar(50) DEFAULT NULL,
  81. PRIMARY KEY (`role_id`)
  82. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  83. -- ----------------------------
  84. -- Records of role
  85. -- ----------------------------
  86. INSERT INTO `role` VALUES ('1', 'admin');
  87. INSERT INTO `role` VALUES ('2', 'ordinary');
  88. -- ----------------------------
  89. -- Table structure for room
  90. -- ----------------------------
  91. DROP TABLE IF EXISTS `room`;
  92. CREATE TABLE `room` (
  93. `id` int(11) NOT NULL AUTO_INCREMENT,
  94. `name` varchar(60) NOT NULL,
  95. `message` varchar(200) DEFAULT NULL,
  96. -- 物理位置
  97. `position` varchar(200) DEFAULT NULL,
  98. -- 会议室图片
  99. `image` varchar(200) DEFAULT NULL,
  100. -- 容量
  101. `capacity` int(11) DEFAULT NULL,
  102. -- 会议室区域
  103. `region` varchar(60) DEFAULT NULL,
  104. -- 会议室二维码
  105. `code` varchar(60) DEFAULT NULL,
  106. PRIMARY KEY (`id`)
  107. ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
  108. -- ----------------------------
  109. -- Records of room
  110. -- ----------------------------
  111. INSERT INTO `room` VALUES ('1', 'Cpeco会议室', '小会议室,适合十人小型会议,含多媒体');
  112. INSERT INTO `room` VALUES ('2', 'Meos会议室', '大型会议室,可使用多媒体,适合开会、讲座');
  113. INSERT INTO `room` VALUES ('3', 'Wellzoom会议室', '小会议室,适合十人小型会议,含多媒体');
  114. INSERT INTO `room` VALUES ('4', '上格云大会议室', '大型会议室,可使用多媒体,适合开会、讲座');
  115. INSERT INTO `room` VALUES ('5', '尚格云小会议室', '小会议室,适合十人小型会议,含多媒体');
  116. INSERT INTO `room` VALUES ('6', 'Beecom会议室', '小会议室,适合十人小型会议,含多媒体');
  117. -- ----------------------------------------
  118. -- Table structure for Facility--会议室设备
  119. -- ----------------------------------------
  120. Drop TABLE IF EXISTS `facility`;
  121. CREATE Table `facility`(
  122. `id` int(11) NOT NULL AUTO_INCREMENT,
  123. `room_id` int(11) NOT NULL,
  124. `facility_name` varchar(60) NOT NULL,
  125. PRIMARY KEY (`id`),
  126. KEY `fk_facility_room` (`room_id`),
  127. CONSTRAINT `fk_facility_room` FOREIGN KEY (`room_id`) REFERENCES `room` (`id`)
  128. )ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8;
  129. -- ----------------------------
  130. -- Records of Facility
  131. -- ----------------------------
  132. -- ----------------------------
  133. -- Table structure for user
  134. -- ----------------------------
  135. DROP TABLE IF EXISTS `user`;
  136. CREATE TABLE `user` (
  137. `id` varchar(32) NOT NULL,
  138. `password` varchar(32) NOT NULL,
  139. `role` int(11) DEFAULT NULL,
  140. PRIMARY KEY (`id`),
  141. KEY `fk_user_role` (`role`),
  142. CONSTRAINT `fk_user_role` FOREIGN KEY (`role`) REFERENCES `role` (`role_id`)
  143. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  144. -- ----------------------------
  145. -- Records of user
  146. -- ----------------------------
  147. INSERT INTO `user` VALUES ('201801', '123', '2');
  148. INSERT INTO `user` VALUES ('admin', 'emadmin', '1');
  149. INSERT INTO `user` VALUES ('ordinary', '123456', '2');
  150. -- ----------------------------------------
  151. -- Table structure for roomauthority--会议室权限
  152. -- ----------------------------------------
  153. Drop TABLE IF EXISTS `room_authority`;
  154. CREATE Table `room_authority`(
  155. `id` int(11) NOT NULL AUTO_INCREMENT,
  156. `room_id` int(11) NOT NULL,
  157. `user_id` varchar(32) NOT NULL,
  158. PRIMARY KEY (`id`),
  159. KEY `fk_authority_room` (`room_id`),
  160. CONSTRAINT `fk_authority_room` FOREIGN KEY (`room_id`) REFERENCES `room` (`id`),
  161. KEY `fk_authority_user` (user_id),
  162. CONSTRAINT `fk_authority_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
  163. )ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8;
  164. -- ----------------------------
  165. -- Records of roomauthority
  166. -- ----------------------------