Web開發(fā)中，需要使用數(shù)據(jù)庫存儲數(shù)據(jù)。這些數(shù)據(jù)大多數(shù)都是相互關聯(lián)的，存儲和使用這樣的數(shù)據(jù)，我常用關系型數(shù)據(jù)庫Mysql來實現(xiàn)。這里，我設計一個用戶數(shù)據(jù)模型，用來舉例說明這些數(shù)據(jù)之間的關系。
獨立的數(shù)據(jù)
我規(guī)劃了一張表user，用來存儲用戶基礎信息，字段包含主鍵id和用戶名username，看起來這張表是這樣的：
| id | username |
|---|---|
| 1 | 張三 |
| 2 | 李四 |
| 3 | 王五 |
當我們查詢user表時，就會得到包含這兩個字段的數(shù)據(jù)行。這樣的數(shù)據(jù)，與其他表沒有任何關聯(lián)，和表本身也沒有關聯(lián)，可以被稱為獨立的數(shù)據(jù)，這樣的數(shù)據(jù)，非常適合存儲到非關系數(shù)據(jù)庫中。不過，我們的用戶表還會修改設計，因為，用戶表會關聯(lián)其他表。
一對一關系
我們的用戶表中，字段太少了，不能描述用戶的豐富屬性。這時候，我們有兩種選擇：
1. 給用戶表增加字段
2. 使用一張新的表格（用戶信息表user_info）來存儲用戶擴展信息
直接給用戶表增加字段，適合用戶字段本身不多，并且增加的字段不會影響其他字段的情況；否則，我們可以新建一張表來存儲這些用戶信息。用戶信息表設計如下：
| id | sex | |
|---|---|---|
| 1 | 男 | zs@test.com |
| 2 | 男 | ls@test.com |
| 3 | 男 | ww@test.com |
那么，兩張表如何關聯(lián)呢？
根據(jù)設計要求，一條用戶表的數(shù)據(jù)，對應一條用戶信息表的數(shù)據(jù)，這樣的對應關系，稱為一對一關系。既然如此，我們可以給用戶表增加一個字段，存儲對應用戶信息表的主鍵。這樣一來，我們想要知道用戶的擴展信息，需要兩步操作：１、查找用戶表，找到用戶的關聯(lián)用戶信息表的主鍵。２、根據(jù)用戶信息表的主鍵，查找到用戶的擴展信息。
根據(jù)用戶信息表的一條數(shù)據(jù)的主鍵，到用戶表中，也可以查詢到用戶表中的具體數(shù)據(jù)。
更改后的用戶表如下：
| id | username | info_id |
|---|---|---|
| 1 | 張三 | 1 |
| 2 | 李四 | 2 |
| 3 | 王五 | 3 |
當然，我們也可以將關聯(lián)主鍵的字段放到用戶信息表中，這樣的話，可以保持用戶表字段較少，那么我們就不要修改用戶表，用戶信息表修改如下：
id sex user_id 1 男 zs@test.com 1 2 男 ls@test.com 2 3 男 ww@test.com 3 甚至，我們可以用戶表存儲用戶信息表的對應主鍵，并且用戶表存儲用戶信息表的主鍵，這樣做會增加一些存儲成本，不過可以提高可讀性。
多對一關系
多對一關系，也叫作一對多關系。網站一般會對用戶進行分組，我們也需要設計用戶分組表，如下：
| id | groupname |
|---|---|
| 1 | 管理員 |
| 2 | 會員 |
一個用戶分組中包含了若干個用戶，如何存儲這樣的關系呢？我們可以考慮，在用戶組表中增加一個字段，來存儲其包含的用戶的主鍵，如下：
| id | groupname | user_id_list |
|---|---|---|
| 1 | 管理員 | 1,2 |
| 2 | 會員 | 3 |
我們知道，關系數(shù)據(jù)庫的字段只能存儲簡單的數(shù)據(jù)類型，例如數(shù)字，字符串，時間等，不支持存儲一個列表。所以這里，我們需要將列表數(shù)據(jù)轉換成一個字符串來存儲，取出來用的時候，我們需要再將其轉換成列表。每次用戶組的用戶變動時，需要對字段user_id_list改動時，則變得麻煩。這時候，推薦另一種多對一的關聯(lián)方式：在用戶表中添加字段group_id，如下：
| id | username | info_id | group_id |
|---|---|---|---|
| 1 | 張三 | 1 | 1 |
| 2 | 李四 | 2 | 1 |
| 3 | 王五 | 3 | 2 |
更改用戶組的用戶成員時候，只需要修改對用用戶的group_id即可，查詢時根據(jù)group_id查詢即可。
多對多關系
不同的用戶組權限不同，我們需要設計一張權限表，如下：
| id | permissions |
|---|---|
| 1 | 禁用用戶 |
| 2 | 修改自己的信息 |
管理員不僅可以修改自己的信息，還可以禁用其他用戶，而會員只能修改自己的信息。這時候我們可以發(fā)現(xiàn)，管理員對應的權限有兩個：禁用用戶和修改自己的信息。而修改自己的信息這項權限，同時被兩個用戶組擁有：管理員和會員。想這樣的關系，是典型的多對多關系。
如何根據(jù)這樣的關系，設計表呢？
根據(jù)之前的經驗，我們會想到，給用戶組表或者權限表增加字段，用來存儲其對應的關系表的主鍵。使用這樣的辦法，我們就會陷入到多對一關系中的不良案例中——利用一個字段存儲多個關系主鍵。
這時候，我們需要跳出原有思維，新建一張用戶組－權限關系表(group_permissions)，來存儲他們的多對多關系，如下：
| id | group_id | permissions_id |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 2 |
使用查詢語句select permissions_id from group_permissions where group_id=1來查詢管理員擁有的權限id列表；使用查詢語句select group_id from group_permissions where permissions_id=2來查詢擁有需改自己的信息的用戶組id列表。
自關聯(lián)一對一關系
網站設計了推薦機制，如果一個用戶通過另一個用戶推薦注冊，那么保存這個用戶的推薦人。分析得知，一個用戶的推薦人只能是另一個已存在的用戶，這是一對一關系，區(qū)別是，這個一對一關系，關聯(lián)的是用戶表本身。
解決方案仍然是老辦法，給用戶表增加一個字段recommended_id，用來關聯(lián)其對應的推薦人。用戶表如下：
| id | username | info_id | group_id | recommended_id |
|---|---|---|---|---|
| 1 | 張三 | 1 | 1 | null |
| 2 | 李四 | 2 | 1 | 1 |
| 3 | 王五 | 3 | 2 | 2 |
自關聯(lián)多對一關系
網站設計黑名單機制，用戶可以拉黑不喜歡的其他用戶。分析得知，一個用戶可以拉黑多個其他用戶，那么這是一個自關聯(lián)的多對一關系。
就像之前的多對一關系那樣，我們嘗試使用增加字段的方式來解決這個問題。設計如下：
| id | username | info_id | group_id | recommended_id | blacklist_id_list |
|---|---|---|---|---|---|
| 1 | 張三 | 1 | 1 | null | 2, 3 |
| 2 | 李四 | 2 | 1 | 1 | 1 |
| 3 | 王五 | 3 | 2 | 2 | 1, 2 |
可以看出來，又陷入到了單一字段存儲多個關系主鍵的問題了。解決辦法也一樣，單獨創(chuàng)建一張黑名單關系表(blacklist)來存儲黑名單關系，如下：
| id | user_id | blacklist_user_id |
|---|---|---|
| 1 | 1 | 2 |
| 2 | 1 | 3 |
| 3 | 2 | 1 |
| 4 | 3 | 1 |
| 5 | 3 | 2 |
自關聯(lián)多對多關系
網站需要增加粉絲和關注機制，用戶可以關注其他用戶，用戶可以得知自己有哪些粉絲。分析得知，這是典型的自關聯(lián)多對多關系。
嘗試使用一張單獨的粉絲關系表(fans)來存儲分析關系，如下：
| id | user_id | fans_id |
|---|---|---|
| 1 | 1 | 2 |
| 2 | 1 | 3 |
| 3 | 2 | 1 |
| 4 | 3 | 1 |
| 5 | 3 | 2 |
經簡單的驗證，可以得知，這樣的設計可以完全滿足粉絲機制的需求。
總結
通過網站用戶和權限的簡單設計案例，我們梳理了獨立數(shù)據(jù)、一對一關系、多對一關系、多對多關系、自關聯(lián)一對一關系、自關聯(lián)多對一關系和自關聯(lián)多對多關系的需求分析和設計實現(xiàn)，可以自如地處理這些復雜的關聯(lián)關系。
最終的字段和數(shù)據(jù)如下：
user
| id | username | info_id | group_id | recommended_id |
|---|---|---|---|---|
| 1 | 張三 | 1 | 1 | null |
| 2 | 李四 | 2 | 1 | 1 |
| 3 | 王五 | 3 | 2 | 2 |
user_info
| id | sex | |
|---|---|---|
| 1 | 男 | zs@test.com |
| 2 | 男 | ls@test.com |
| 3 | 男 | ww@test.com |
group
| id | groupname |
|---|---|
| 1 | 管理員 |
| 2 | 會員 |
permissions
| id | permissions |
|---|---|
| 1 | 禁用用戶 |
| 2 | 修改自己的信息 |
group_permissions
| id | group_id | permissions_id |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 2 |
blacklist
| id | user_id | blacklist_user_id |
|---|---|---|
| 1 | 1 | 2 |
| 2 | 1 | 3 |
| 3 | 2 | 1 |
| 4 | 3 | 1 |
| 5 | 3 | 2 |
fans
| id | user_id | fans_id |
|---|---|---|
| 1 | 1 | 2 |
| 2 | 1 | 3 |
| 3 | 2 | 1 |
| 4 | 3 | 1 |
| 5 | 3 | 2 |
無戒365訓練營 第九篇
