Java 数据字典设计之数据库设计(一)

本文以 Spring Boot 应用、MySQL 数据库为例。

数据字典表:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- 数据字典表
DROP TABLE
IF EXISTS `dictionary`;

CREATE TABLE `dictionary` (
`id` BIGINT(20) UNSIGNED AUTO_INCREMENT COMMENT 'id',
`key` VARCHAR(255) NOT NULL COMMENT '字典键',
`key_name` VARCHAR(255) NOT NULL COMMENT '字典键名',
`value_name` VARCHAR(255) NOT NULL COMMENT '字典值名',
`value_slug` VARCHAR(255) NOT NULL COMMENT '字典值别名',
`value` TEXT COMMENT '字典值',
`editable` TINYINT(1) UNSIGNED DEFAULT NULL COMMENT '是否可编辑(0=不可编辑,1=可编辑,默认=1)',
`dictionary_category_id` BIGINT(20) UNSIGNED NOT NULL COMMENT '字典分类 id',
`sort` BIGINT(20) UNSIGNED DEFAULT 0 COMMENT '排序',
`remark` VARCHAR(255) DEFAULT '' COMMENT '备注',
`gmt_modified` DATETIME DEFAULT NOW() COMMENT '更新时间',
`gmt_created` DATETIME DEFAULT NOW() COMMENT '创建时间',
PRIMARY KEY (`id`)
)
ENGINE = INNODB
DEFAULT CHARACTER
SET = utf8mb4
COLLATE = utf8mb4_general_ci
AUTO_INCREMENT = 1
ROW_FORMAT = DYNAMIC
COMMENT = '数据字典表';

数据字典分类表:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 数据字典分类表
DROP TABLE
IF EXISTS `dictionary_category`;

CREATE TABLE `dictionary_category` (
`id` BIGINT(20) UNSIGNED AUTO_INCREMENT COMMENT 'id',
`name` VARCHAR(255) NOT NULL COMMENT '分类名称',
`parent_id` BIGINT(20) UNSIGNED DEFAULT NULL COMMENT '父级分类 id',
`sort` BIGINT(20) DEFAULT 0 COMMENT '排序',
`remark` VARCHAR(255) DEFAULT '' COMMENT '备注',
`gmt_modified` DATETIME DEFAULT NOW() COMMENT '更新时间',
`gmt_created` DATETIME DEFAULT NOW() COMMENT '创建时间',
PRIMARY KEY (`id`)
)
ENGINE = INNODB
DEFAULT CHARACTER
SET = utf8mb4
COLLATE = utf8mb4_general_ci
AUTO_INCREMENT = 1
ROW_FORMAT = DYNAMIC
COMMENT = '数据字典分类表';

数据字典表对应的实体类:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
package com.gioov.spiny.system.entity;

import java.io.Serializable;
import java.util.Date;

/**
* @author godcheese
* @date 2018/4/23 20:44
*/
public class DictionaryEntity implements Serializable {

private static final long serialVersionUID = -4000696333938261490L;

/**
* id
*/
private Long id;

/**
* 字典键
*/
private String key;

/**
* 字典键名
*/
private String keyName;

/**
* 字典值名
*/
private String valueName;

/**
* 字典值别名
*/
private String valueSlug;

/**
* 字典值
*/
private String value;

/**
* 是否可编辑
*/
private Integer editable;

/**
* 字典分类 id
*/
private Long dictionaryCategoryId;

/**
* 排序
*/
private Long sort;

/**
* 备注
*/
private String remark;

/**
* 更新时间
*/
private Date gmtModified;

/**
* 创建时间
*/
private Date gmtCreated;


public Long getId() {
return id;
}

public void setId(Long id) {
this.id = id;
}

public String getKey() {
return key;
}

public void setKey(String key) {
this.key = key;
}

public String getKeyName() {
return keyName;
}

public void setKeyName(String keyName) {
this.keyName = keyName;
}

public String getValueName() {
return valueName;
}

public void setValueName(String valueName) {
this.valueName = valueName;
}

public String getValueSlug() {
return valueSlug;
}

public void setValueSlug(String valueSlug) {
this.valueSlug = valueSlug;
}

public String getValue() {
return value;
}

public void setValue(String value) {
this.value = value;
}

public Integer getEditable() {
return editable;
}

public void setEditable(Integer editable) {
this.editable = editable;
}

public Long getDictionaryCategoryId() {
return dictionaryCategoryId;
}

public void setDictionaryCategoryId(Long dictionaryCategoryId) {
this.dictionaryCategoryId = dictionaryCategoryId;
}

public Long getSort() {
return sort;
}

public void setSort(Long sort) {
this.sort = sort;
}

public String getRemark() {
return remark;
}

public void setRemark(String remark) {
this.remark = remark;
}

public Date getGmtModified() {
return gmtModified;
}

public void setGmtModified(Date gmtModified) {
this.gmtModified = gmtModified;
}

public Date getGmtCreated() {
return gmtCreated;
}

public void setGmtCreated(Date gmtCreated) {
this.gmtCreated = gmtCreated;
}

}

数据字典分类表对应的实体类:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
package com.gioov.spiny.system.entity;

import java.io.Serializable;
import java.util.Date;

/**
* @author godcheese
* @date 2018/5/23
*/
public class DictionaryCategoryEntity implements Serializable {

private static final long serialVersionUID = -5867777461580679038L;

private Long id;

/**
* 分类名称
*/
private String name;

/**
* 父级分类 id
*/
private Long parentId;

/**
* 排序
*/
private Long sort;

/**
* 备注
*/
private String remark;

/**
* 更新时间
*/
private Date gmtModified;

/**
* 创建时间
*/
private Date gmtCreated;


public Long getId() {
return id;
}

public void setId(Long id) {
this.id = id;
}

public String getName() {
return name;
}

public void setName(String name) {
this.name = name;
}

public Long getParentId() {
return parentId;
}

public void setParentId(Long parentId) {
this.parentId = parentId;
}

public Long getSort() {
return sort;
}

public void setSort(Long sort) {
this.sort = sort;
}

public String getRemark() {
return remark;
}

public void setRemark(String remark) {
this.remark = remark;
}

public Date getGmtModified() {
return gmtModified;
}

public void setGmtModified(Date gmtModified) {
this.gmtModified = gmtModified;
}

public Date getGmtCreated() {
return gmtCreated;
}

public void setGmtCreated(Date gmtCreated) {
this.gmtCreated = gmtCreated;
}

}