MySQL 表設計

標籤：大致   缺點   code   學生   rem   抽取   一對一   執行   個數   

0、這個部落格中一部分內容是Deolin的個人觀點和習慣。

 

1、把SET del_flag = 1 稱為“移除”（remove），

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

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

 

2、資料表可以分為資訊表，關聯表，字典表。

 

3、資訊表反映的是資料資訊，“資料為王”主要指的就是這類表裡的資料。

資訊表應該有以下欄位

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

insert_time datetime 這條資料第一次執行insert文的時間，不可為空。

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

del_flag tinyint(1) 這條資料是否被移除，不可為空，預設為0。由於資訊表裡的資料往往比較重要，所以應該禁止擦除，使用移除。而“有del_flag欄位”和“使用移除”兩件事情應該同時出現，或同時不出現

 

資訊表可以有以下欄位，但不是“應該有”

insert_id int 插入這條資料的業務上的插入者id，代表的是某一個資訊表的主鍵（user表之類的）。

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

update_id int 與insert_id同理。

update_table char(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,  `del_flag` tinyint(1) NOT NULL DEFAULT ‘0‘,  PRIMARY KEY (`id`))

 

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

除了主鍵，關聯表應該有像資訊表那樣的基礎欄位。

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

第一種

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

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

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

 

第二種

使用主鍵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,  `del_flag` tinyint(1) NOT NULL DEFAULT ‘0‘,  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,  `del_flag` tinyint(1) NOT NULL DEFAULT ‘0‘,  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,  `del_flag` tinyint(1) NOT NULL DEFAULT ‘0‘,  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,  `del_flag` tinyint(1) NOT NULL DEFAULT ‘0‘,  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,  `del_flag` tinyint(1) NOT NULL DEFAULT ‘0‘,  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(11) unsigned zerofill NOT NULL AUTO_INCREMENT,  `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,  `del_flag` tinyint(1) NOT NULL DEFAULT ‘0‘,  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`用datetime，`del_flag`用10。

MySQL 表設計