conference_room.sql 6.0 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195
  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. -- ----------------------------------------
  112. -- Table structure for Facility--会议室设备
  113. -- ----------------------------------------
  114. Drop TABLE IF EXISTS `facility`;
  115. CREATE Table `facility`(
  116. `id` int(11) NOT NULL AUTO_INCREMENT,
  117. `room_id` int(11) NOT NULL,
  118. `facility_name` varchar(60) NOT NULL,
  119. PRIMARY KEY (`id`),
  120. KEY `fk_facility_room` (`room_id`),
  121. CONSTRAINT `fk_facility_room` FOREIGN KEY (`room_id`) REFERENCES `room` (`id`)
  122. )ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8;
  123. -- ----------------------------
  124. -- Records of Facility
  125. -- ----------------------------
  126. -- ----------------------------
  127. -- Table structure for user
  128. -- ----------------------------
  129. DROP TABLE IF EXISTS `user`;
  130. CREATE TABLE `user` (
  131. `id` varchar(32) NOT NULL,
  132. `password` varchar(32) NOT NULL,
  133. `role` int(11) DEFAULT NULL,
  134. PRIMARY KEY (`id`),
  135. KEY `fk_user_role` (`role`),
  136. CONSTRAINT `fk_user_role` FOREIGN KEY (`role`) REFERENCES `role` (`role_id`)
  137. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  138. -- ----------------------------
  139. -- Records of user
  140. -- ----------------------------
  141. -- INSERT INTO `user` VALUES ('201801', '123', '2');
  142. -- INSERT INTO `user` VALUES ('admin', 'emadmin', '1');
  143. -- INSERT INTO `user` VALUES ('ordinary', '123456', '2');
  144. -- ----------------------------------------
  145. -- Table structure for roomauthority--会议室权限
  146. -- ----------------------------------------
  147. Drop TABLE IF EXISTS `room_authority`;
  148. CREATE Table `room_authority`(
  149. `id` int(11) NOT NULL AUTO_INCREMENT,
  150. `room_id` int(11) NOT NULL,
  151. `user_id` varchar(32) NOT NULL,
  152. PRIMARY KEY (`id`),
  153. KEY `fk_authority_room` (`room_id`),
  154. CONSTRAINT `fk_authority_room` FOREIGN KEY (`room_id`) REFERENCES `room` (`id`),
  155. KEY `fk_authority_user` (user_id),
  156. CONSTRAINT `fk_authority_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
  157. )ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8;
  158. -- ----------------------------
  159. -- Records of roomauthority
  160. -- ----------------------------