Mysql表單設計

寫著都是比較入門的，都是我剛開始做的筆記。。

1、把SET remove_time = '1970-01-01 00:00:00' 稱為“移除”（remove），

把DELETE FROM table 稱為“擦除”（expunge），

至於“刪除”（delete）、“取消”（cancel），則更像個業務層而不是持久層的概念。

2、數據表可以分為信息表，關聯表，字典表。

3、信息表反映的是數據信息，“數據為王”主要指的就是這類表裡的數據。

信息表應該有以下字段

id int 這條數據的主鍵，默認長度，無符號，自增，前位補零。由於remove_time的存在，所以業務上的字段做主鍵便不再合適，因為新插入的數據很有可能與被移除的數據發生主鍵重複，所以需要一個毫無業務意義的主鍵

insert_time datetime 這條數據第一次執行insert文的時間，不能為空。

update_time datetime 這條數據每次執行update文的時間，可以為空（如果從為被update過，那就應該是空的）。

由於信息表裡的數據往往比較重要，所以應該禁止擦除，使用移除。而“有remove_time字段”和“使用移除”兩件事情應該同時出現，或同時不出現

信息表可以有以下字段，但不是“應該有”

insert_id int 插入這條數據的業務上的插入者id，代表的是某一個信息表的主鍵（user表之類的）。

insert_table varchar(64) 如果業務上不只一類插入者，那麼需要指定是那個信息表。長度定為64，因為表名最長64個字符。

update_id int 與insert_id同理。

update_table varchar(64) 與insert_table同理。

remove_id int 與insert_id同理。

remove_table varchar(64) 與insert_table同理。

如果是整個項目在業務上沒有登錄行為，或是管理員在db上插入/修改的，那麼些字段只能為空了，這也是這些字段不算“應該有”的原因了。

一個信息表的示例可能是這樣的

CREATE TABLE `person` (

`id` int(11) unsigned zerofill NOT NULL AUTO_INCREMENT,

`name` varchar(20) NOT NULL,

`sex` tinyint(1) DEFAULT NULL,

`birthday` date DEFAULT NULL,

`father_age` int(3) DEFAULT NULL, #這個字段可能非常奇怪，但Deolin只是希望能在示例裡有至少一個int字段，並且最好不是age（age跟birthday一起出現的話顯得更加奇怪）

`tuition` decimal(8,2) DEFAULT NULL,

`payment_time` datetime DEFAULT NULL,

`vip_flag` tinyint(1) DEFAULT NULL,

`insert_time` datetime NOT NULL,

`update_time` datetime DEFAULT NULL,

`remove_time` datetime DEFAULT NULL

PRIMARY KEY (`id`)

)

4、關聯表反映的是兩張信息表之間多對多的關係，例如學生表與課程表之間的關係

除了主鍵，關聯表應該有像信息表那樣的基礎字段。

至於主鍵，它的設計有兩種方式

第一種

放棄主鍵id，而是使用被關聯表各自的id作為雙主鍵（如student_id, lesson_id），

這種方式優點是直觀，一眼就能看出是關聯表，而且關聯關係絕對不會重複。

有個缺點，remove_time和移除操作不能有了，因為會發生主鍵重複，所以只能擦除。

第二種

使用主鍵id，優缺點與第一種方式正好相反。

主要適用於兩種場合，一是整個項目有比較強的數據挖掘和分析目地的，即便是關聯關係也要保留下來，

二是關聯關係產生時，會出現一些業務字段（如學生選的課，會有成績，那麼成績字段不應該放在student或lesson中，而是應該放在關聯表裡）

5、字典表反映的是為項目本身提供的各種數據，是對項目擴展性的補充，

（如JSP中option標籤的每一個value屬性，如某個表'kind'之類字段的值解釋，如一些配置屬性），數據量不會很大，

基本上，數據是開發人員錄入，項目不會對它有增、改、移除、擦除操作，只有查詢操作，一個不是特別大的項目只需要一張字典表。

一般只有以下字段

key varchar(255) 主鍵，字典的索引

value varchar(255) 字典的值

group varchar(255) 用來表示key屬於那一類，可以為空

sort int 用來表示key在自己所在group的順序，可以為空

disable tinyint(1) 用來表示這條數據是否被失效，不能為空，默認0

6、信息表之間一對一的關係，設計成兩張表的id字段一致

例如`person`表示例中，每個人都有“學生”和“子女”兩個不同模塊的屬性，可以將它們分別抽取出來

CREATE TABLE `person` (

`id` int(11) unsigned zerofill NOT NULL AUTO_INCREMENT,

`name` varchar(20) NOT NULL,

`sex` tinyint(1) DEFAULT NULL,

`birthday` date DEFAULT NULL,

`vip_flag` tinyint(1) DEFAULT NULL,

`insert_time` datetime NOT NULL,

`update_time` datetime DEFAULT NULL,

`remove_time` datetime DEFAULT NULL,

PRIMARY KEY (`id`) #與其他兩張表的id形成邏輯外鍵

)

CREATE TABLE `student` (

`id` int(11) unsigned zerofill NOT NULL AUTO_INCREMENT,

`tuition` decimal(8,2) DEFAULT NULL,

`payment_time` datetime DEFAULT NULL,

`insert_time` datetime NOT NULL,

`update_time` datetime DEFAULT NULL,

`remove_time` datetime DEFAULT NULL,

PRIMARY KEY (`id`) #與其他兩張表的id形成邏輯外鍵

)

CREATE TABLE `child` (

`id` int(11) unsigned zerofill NOT NULL AUTO_INCREMENT,

`father_age` int(3) DEFAULT NULL,

`father_job` int(3) DEFAULT NULL,

`mother_age` int(3) DEFAULT NULL,

`mother_job` int(3) DEFAULT NULL,

`insert_time` datetime NOT NULL,

`update_time` datetime DEFAULT NULL,

`remove_time` datetime DEFAULT NULL,

PRIMARY KEY (`id`) #與其他兩張表的id形成邏輯外鍵

)

7、信息表之間一對多的關係，在“多”的表中追加一個 `for_表名` 字段（如果其他字段中有單詞for，有時候也會設計成`表名_id`）

例如，一家學校當前有多個學生，一個學生當前只能屬於一個學校

CREATE TABLE `school` (

`id` int(11) unsigned zerofill NOT NULL AUTO_INCREMENT,

`address` varchar(200) DEFAULT NULL,

`insert_time` datetime NOT NULL,

`update_time` datetime DEFAULT NULL,

`remove_time` datetime DEFAULT NULL,

PRIMARY KEY (`id`)

)

CREATE TABLE `student` (

`id` int(11) unsigned zerofill NOT NULL AUTO_INCREMENT,

`for_school` int(11) NOT NULL DEFAULT '0', #與school.id形成邏輯外鍵

`name` varchar(20) DEFAULT NULL,

`insert_time` datetime NOT NULL,

`update_time` datetime DEFAULT NULL,

`remove_time` datetime DEFAULT NULL,

PRIMARY KEY (`id`)

)

8、關聯表的命名，一般是兩張“多”的表的表名用2鏈接

例如一個學生選擇多門課程，一門課程供多名學生選擇

CREATE TABLE `selectable_lesson` (

`id` int(11) unsigned zerofill NOT NULL AUTO_INCREMENT,

#省略

PRIMARY KEY (`id`)

)

CREATE TABLE `normal_student` (

`id` int(11) unsigned zerofill NOT NULL AUTO_INCREMENT,

#省略

PRIMARY KEY (`id`)

)

CREATE TABLE `normal_student2selectable_lesson` ( #意為normal student to selectable lesson

`id` int(

`for_normal_student` int(11) NOT NULL DEFAULT '0', #與normal_student.id形成邏輯外鍵

`for_selectable_lesson` int(11) NOT NULL DEFAULT '0', #與selectable_lesson.id形成邏輯外鍵

`score` int(3) DEFAULT NULL,

`insert_time` datetime NOT NULL,

`update_time` datetime DEFAULT NULL,

`remove_time` datetime DEFAULT NULL,

PRIMARY KEY (`id`)

)

9、信息表的中字段名中不出現表名，

如不採用user.username，而是採用user.name

11、信息表中涉及到“屬於什麼類型”、“是哪種分類”概念的字段，用`kind`表示，如user.kind

涉及到以上概念信息表，用諸如`food_type`來表示。

10、類型

字符串長度不變的用char(n)，

字符串能大致確定範圍的用varchar(n)，

字符串範圍超過65532的用text，

年齡之類的字段通常用int(n)，

上限不定的個數類字段通常用int默認，即最長11，

更長時用bigint，

日期用date，

日期+時間用datetime，

時間用time，

區分類字段（如“性別”，“直轄市”等）用tiny(1)（並在Java代碼中定義好規約），

是/否類字段用tiny(1)，

價格類字段用decimal(8,2)，-99,999,999.99 ~ 99,999,999.99，

電話/手機用varchar(20)，

`id`用int，`insert_time`和`update_time`和`remove_time`用datetime

閱讀更多 未來的語言 的文章

關鍵字: 移除 Java JSP