[資料庫] 關聯介紹 一對一、一對多、多對多

前言

會想要寫這篇是因為最近在摸php框架laravel

在ORM的地方間接學到了資料庫的Relationships

才發現以前好傻好天真,竟然規劃出這樣的table…

別懷疑我真的幹過這種蠢事@@

posts跟tags其實是多對多的關係

應該要建立一個intermediary table才是正確的做法((以下會詳細說明

因此,必須好好筆記一下最近學到的資料庫Relationships

 

關聯介紹

假設有個部落格文章在資料庫裡的table長這樣,把所有文章所需的資料都集中在一個table

不難發現,文章的tag有重複的現象

而且如果今天,我又新增了一篇文章,tag不小心手誤打成了Databases

是不是又歸成了新的tag了

或者,我今天想要有兩個tags,三個tags,四個tags

那是不是又要重蹈覆轍建立好多columns了@@

因此,在現在的資料庫中,普遍都會把共同常用的資料獨立成新的table

再利用主鍵(primary key)與外鍵(foreign key)串起table與table間的關係

主鍵:
可以拿來識別所有資料的column,以剛剛的table來說就是id。id=1可以找到第一筆資料,以此類推。
外鍵:
其他table的主鍵,拿來給目前的table參考過去用。

如此一來就不會發生手誤打錯的情形了

這種資料庫的形式就叫做「關聯式資料庫」。

 

關聯種類


 

一對一關聯

甲乙兩個table裡面各自存在一筆記錄,可以使用其主鍵,對應至另一個table的一筆記錄。

拿下圖當例子,假設我有access兩個table的權限,只要SELECT學號(主鍵)107000001再做JOIN

就可以正確且只有得到小明的完整資料

(不會是其他人的,因為學號是主鍵,不會重複)

當然有些人會問說為什麼不放在同個table省去JOIN的麻煩

table分開設計的好處是可以直接設定該table被存取的權限

讓不相干的人只能存取到學生的名單

只有老師可以查看學生的個人資料

舉其它例子,像是公司內人資部門有員工基本資料

財務部門有員工薪資資料

有最高權限的總經理可以以員工代號去SELECTJOIN出這名同仁的完整資料(舉這樣的例子好像怪怪的…)


 

一對多關聯

甲table的一筆記錄可以對應到乙table的多筆記錄;
但,乙table的一筆記錄只能對應到甲table的一筆記錄。

另一個相同的名詞叫多對一,其實一對多與多對一只有資料表在表達時順序上的差別而已

如上圖,我們把科系名稱獨立成一個table

這樣就不會發生系助不小心key in科系時打錯字,造成用科系SELECT不到正確資料的情況

或如果某個科系被迫改名時,只要改科系的那個table一處即可(我怎麼都舉些怪怪的例子…)


 

多對多關聯

甲table的一筆記錄可以對應到乙table的多筆記錄;
乙table的一筆記錄也可以對應到甲table的多筆記錄。

聽起來很饒舌沒關係,我們拿一開始的table舉例,看到2nd Post這筆記錄

他的tag1對應到名叫SQL的tag
tag2對應到名叫PHP的tag
tag3對應到名叫Database的tag

同樣地,SQL這個tag對應到title叫First Post的post
以及2nd Post的post

這種雙方都互相對應多比對方記錄的模式則叫多對多關聯

我也要順便澄清上面的圖表不是正統作法

正統設計多對多關聯的方式其實是在中間建立另一個intermediary table(中文我不知道叫什麼…)

藉由posts對tag_post一對多關聯
以及tags對tag_post一對多關聯

來實現兩個table之間的多對多的relationship

 

搭配圖例再來一次

我們把原本設計很爛有重複相同用途的column(tag1、tag2、tag3)刪除

新增一個intermediary table來存放tag_id跟post_id

如此一來就可以實現兩個table多對多

而且再也不用tag1、tag2、…、tag100,以及一堆空值的格子及數量限制的問題了(歡呼)

真是可喜可賀(撒花)~

 

最後,follow上下圖的綠色、藍色、橘色箭頭再走一次
相信大家能更深刻理解這個做法的。


12 則迴響

  • LH

    2018-08-25

    很清楚的整理! 謝謝 :):):)
    – 一樣在 Laravel 裡才學到 relation 的 Laraveler

    回復
    • Andy Wu

      2018-08-26

      :)))

      回復
  • ilo

    2018-11-08

    清楚的圖示與說明(拇指)

    回復
    • Andy Wu

      2018-11-09

      👍👍👍

      回復
  • tony

    2018-11-11

    講的很清楚

    回復
    • Andy Wu

      2018-11-13

      😃

      回復
  • 包子

    2019-01-18

    極度清楚,太威了,推!

    回復
    • Andy Wu

      2019-01-19

      ☺️

      回復
  • P

    2019-04-25

    舉例很淺顯易懂
    給你一個讚~!!

    回復
    • Andy Wu

      2019-04-25

      ^^

      回復
  • Song

    2019-06-11

    正在思索如何建立多對多,恰好看到這篇。
    有你真好 :)

    回復
    • Andy Wu

      2019-06-14

      :)

      回復

發佈留言