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

前言

會想要寫這篇是因為最近在摸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上下圖的綠色、藍色、橘色箭頭再走一次
相信大家能更深刻理解這個做法的。


16 則迴響

  • 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

      :)

      回復
  • gavin

    2021-05-20

    請問在多對多中,當要改變post的tag,例如原本3項改成二項,或改變內容時,要如何下指令讓tag_post的表跟著改變呢?

    回復
    • Andy Wu

      2021-06-15

      如果是三項遍兩項只要在tag_post那個intermediary table裡面把不要的刪除就行了唷:)

      回復
  • Roy

    2021-10-29

    回復
  • Rick

    2021-11-18

    Hi Andy,
    謝謝您的文章, 讓我更了解了intermediary table,
    有個intermediary table問題想請教您, 希望您在閒暇之餘有空能指點解惑 : )

    我查了許多別人分享的經驗, 在設計database 關於折扣時 都是把折扣表 和商品表 做聯結,
    但我想要的是 直接在發票上依照節日促銷活動給予10%或是20%折扣, 例如 母親節時總額度有10%折扣,然後去顯示出Discount 有多少.

    我設計一個invoice table 和一個 promotion table,
    invoice table 中有一個filed 叫 Discount , 此discount 不會顯示折扣%數,而是顯示"折扣金額" (金額x折扣趴數), 此discount 的折扣%數 取自 promotion table 中的Discount_Percentage

    若直接將兩個表作PK 跟FK 的連結 (在Invoice table中建立一個Promotion ID當做FK), 但以我的認知是用這方法只會在invoice table中呈現出promotion ID
    (我不確定這樣想是正確還是錯誤的...)
    所以我想需要一個 中介 的表格叫 Invoice_Promotion,
    裡面有InvoiceID (FK) 跟 PromotionID(FK) 然後也有fild叫Discount_percentage,
    需要如此設計嗎? 還是這樣有點多此一舉?

    謝謝您看完我的提問

    回復

發佈留言