DB设计工具——dbschema

 

Preface

* *

亚搏app官方网站,    I’ve got a db design job about
meeting room booking system last week.There’re many suitable tools which
can be used to handle this kind of job such as power
designer,ERwin,HeidiSQL,dbschema,etc.Today,I’m gonna demonstrate the
last one —— dbschema.This is the official website:**https://www.dbschema.com

 

Introduce

 

   
dbschema is a rather simply used tool even you’re a novice in designing
db system.The dbschema which is downloaded on official website only free
for 15 days,then you have to pay for license for later useage but
there’s no limit in function at all.It provide two modes in designing
layout of your system.One is offline mode and the other one is
connecting to db servers.You can easily synchronize tables of database
with your designed tables as soon as possible by refreshing them from
time to time.It also supports almost all popular rdbms such as
oracle,db2,MySQL,postgreSQL.There’re many key features which you can
found in the homepage of official website.I’m not going to describe each
one of them.

 

Procedure

 

*www.yabovip4.com,    The meeting room booking
system(I’ll call it “mrbs” .) I  contains four
tables:employee,department,conference_room,room_reservation.The detail
of tables shows below.

*

 

employee table

1 id             自增id                int(11)
2 user_id        工号                  int(11)
3 user_name      用户名称              varchar(20)
4 user_phone     用户手机号             bigint
5 user_email     用户邮箱              varchar(50)
6 user_dept_id   用户所在部门id         int(11)
7 user_status    在职、离职等           tinyint(4)
8 create_time    用户创建时间           datetime
9 update_time    用户信息修改时间       datetime

DB设计工具——dbschema。 

**department
tableDB设计工具——dbschema。**

1 id              自增id                 int(11)
2 dept_id         部门id                 int(11)
3 dept_name       部门名称               varchar(30)
4 parent_id       父级id                 tinyint(4)
5 tlevel          层级id                 tinyint(4)
6 create_time     部门创建时间            datetime    
7 update_time     部门信息修改时间        datetime

 

**conference_room
table**

1 id                 自增id                int(11)
2 room_id            会议室id              int(11)
3 room_building_id   楼号                  int(11)
4 room_num           房间号                int(11)
5 room_max_num       最大容纳人数           int(11)
6 room_status        会议室状态            tinyint(4)
7 create_time        会议室创建时间         datetime
8 update_time        会议室信息修改时间     datetime

DB设计工具——dbschema。 

**room_reservation
table**

 1 会议室预定表(room_reservation)
 2 id                  自增id               int(11)
 3 book_id             预定工单id            int(11)
 4 book_room_id        预定会议室id          int(11)
 5 book_start_time     预定开始时间          datetime
 6 book_stop_time      预定结束时间          datetime
 7 book_user_id        预定人id              int(11)
 8 book_usage          预定用途              varchar(200)
 9 book_status         预定工单状态           tinyint(4)
10 create_time         预定工单创建时间       datetime
11 update_time         预定工单修改时间       datetime

 

**Configure
the database connection.**

www.yabovip4.com 1

 

Use
mouse to create target tables in dbschema.

 www.yabovip4.com 2

 

Check
the primary key & unique key(even other keys but I’m not creating them
ye).

 www.yabovip4.com 3

 

Check
the foreign key.

 www.yabovip4.com 4

 

After
you click ok button,the table will be created in “mrbs”
database.

www.yabovip4.com 5

 

Check
the tables in “mrbys”.

 1 root@localhost:mysql3306.sock [mrbs]>show tables;
 2 +------------------+
 3 | Tables_in_mrbs   |
 4 +------------------+
 5 | conference_room  |
 6 | department       |
 7 | employee         |
 8 | room_reservation |
 9 +------------------+
10 4 rows in set (0.01 sec)
11 
12 root@localhost:mysql3306.sock [mrbs]>show create table employee\G
13 *************************** 1. row ***************************
14        Table: employee
15 Create Table: CREATE TABLE `employee` (
16   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
17   `user_id` int(10) unsigned NOT NULL COMMENT '工号',
18   `user_name` varchar(20) NOT NULL COMMENT '用户名称',
19   `user_phone` bigint(20) unsigned NOT NULL COMMENT '用户手机号',
20   `user_email` varchar(50) DEFAULT NULL COMMENT '用户邮箱',
21   `user_dept_id` int(10) unsigned NOT NULL COMMENT '用户所在部门id',
22   `user_status` tinyint(3) unsigned NOT NULL COMMENT '是否在职',
23   `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '用户创建时间',
24   `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '用户信息修改时间',
25   PRIMARY KEY (`id`),
26   UNIQUE KEY `uk_user_id` (`user_id`)
27 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表'
28 1 row in set (0.00 sec)
29 
30 root@localhost:mysql3306.sock [mrbs]>show create table department\G
31 *************************** 1. row ***************************
32        Table: department
33 Create Table: CREATE TABLE `department` (
34   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
35   `dept_id` int(10) unsigned NOT NULL COMMENT '部门id',
36   `dept_name` varchar(30) NOT NULL COMMENT '部门名称',
37   `parent_id` tinyint(3) unsigned NOT NULL,
38   `tlevel` tinyint(3) unsigned NOT NULL COMMENT '层级',
39   `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '部门创建时间',
40   `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '部门信息修改时间',
41   PRIMARY KEY (`id`),
42   UNIQUE KEY `uk_dept_id` (`dept_id`)
43 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='部门表'
44 1 row in set (0.00 sec)
45 
46 root@localhost:mysql3306.sock [mrbs]>show create table conference_room\G
47 *************************** 1. row ***************************
48        Table: conference_room
49 Create Table: CREATE TABLE `conference_room` (
50   `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
51   `room_id` int(10) unsigned NOT NULL COMMENT '会议室id',
52   `room_building_id` int(10) unsigned NOT NULL COMMENT '楼号',
53   `room_num` int(10) unsigned NOT NULL COMMENT '房间号',
54   `room_max_num` int(10) unsigned NOT NULL COMMENT '最大容纳人数',
55   `room_status` tinyint(3) unsigned NOT NULL COMMENT '会议室状态',
56   `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '会议室创建时间',
57   `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '会议室信息修改时间',
58   PRIMARY KEY (`id`),
59   UNIQUE KEY `uk_room_id` (`room_id`)
60 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='会议室表'
61 1 row in set (0.00 sec)
62 
63 root@localhost:mysql3306.sock [mrbs]>show create table room_reservation\G
64 *************************** 1. row ***************************
65        Table: room_reservation
66 Create Table: CREATE TABLE `room_reservation` (
67   `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
68   `book_id` int(10) unsigned NOT NULL COMMENT '预定工单id',
69   `book_room_id` int(10) unsigned NOT NULL COMMENT '预定会议室id',
70   `book_start_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '预定开始时间',
71   `book_stop_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '预定结束时间',
72   `book_user_id` int(10) unsigned NOT NULL COMMENT '预定人id',
73   `book_usage` varchar(200) NOT NULL COMMENT '预定用途',
74   `book_status` tinyint(3) unsigned NOT NULL COMMENT '预定工单状态',
75   `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '预定工单创建时间',
76   `update_time` date DEFAULT NULL COMMENT '预定工单修改时间',
77   PRIMARY KEY (`id`),
78   UNIQUE KEY `uk_book_id` (`book_id`)
79 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='会议室预定表'
80 1 row in set (0.00 sec)

 

  

发表评论

电子邮件地址不会被公开。 必填项已用*标注