PostgreSQL 正規化探討 Part2

前言

前文感謝大家的回應.其中Eric Tam大大有提到多名女優合演同一片的情境.
所以特別再補充本篇.

共演的情境

array 的方式

若能夠使用array 裡面的元素都能夠使用references 就能夠很輕鬆的處理了.
可惜這方式,似乎只有討論試做,並未正式的支持.

其實之前的範例是簡化了

之前的範例中, videos 其實只是一個對應的video 與 contract 的中介table,video 相關的資訊會有另一個table 存放,而且是上一級.
這樣就能夠將共演片產生多筆在video中,分別對應每個共演女優與公司的contract.每筆對應單獨的video詳細資訊那一級.

實際的範例

以 DV-1368 為例,這是 小島みなみ 與 奧田咲 在 Alice Japan 的共演片.

-- 新增 奧田咲 在 s1212a.gal

insert into s1212a.gal values
(5, '奧田咲');

-- 新增 奧田咲 與 Alice Japan 配對在 s1212a.contract
insert into s1212a.contract values
(8, 5, 1);

-- 新增 DV-1368 兩筆到 s1212a.videos

insert into s1212a.videos values
(12, 1, 'DV-1368'),(13, 8, 'DV-1368');

select *
  from s1212a.current_vidoes;
  
   gname    |    cname    |        vids         
------------+-------------+---------------------
 奧田咲     | Alice Japan | {DV-1368}
 葵つかさ   | Alice Japan | {DV-1195}
 葵つかさ   | S1          | {SNIS–436,SSIS-974}
 あやみ旬果 | PRESTIGE    | {EDD-209,ABP-356}
 あやみ旬果 | S1          | {SSNI-203}
 小島みなみ | Alice Japan | {DV-1279,DV-1368}
 小島みなみ | S1          | {SSIS-911,SNIS-133}
 鈴村あいり | PRESTIGE    | {EDD-218,ABF-061}
(8 rows)

增加刪除同樣番號的機制

若日本沒有AV新法,其實是不需要這樣補充的.
但是若是採取嚴格的方式,只要有一名女優提出下架,不管那女優演出占比多少,就強制下架.所以需要補充這樣的機制.
只要建立一個 after delete trigger 將相同番號的資料從 s1212a.videos 刪除,當然這樣刪除時,之前的另一個trigger也會發生作用,會將資料存到 s1212bak.videos 中.
來看實際操作吧.

create or replace function s1212a.f_videos_ad_del()
returns trigger
language plpgsql as
$code$
begin
  delete 
    from s1212a.videos
   where vid = old.vid;
   
  return old;
end;
$code$;

create trigger tri_ad_videos
after delete on s1212a.videos
for each row execute function s1212a.f_videos_ad_del();

----
-- 假設 奧田咲 要求下架 Alice Japan 的影片
delete 
  from s1212a.contract c
 using s1212a.gal g
     , s1212a.company co
 where c.gal_id = g.id
   and c.com_id = co.id
   and g.gname = '奧田咲'
   and co.cname = 'Alice Japan';
   
-- 檢查 

select *
  from s1212a.current_vidoes;

   gname    |    cname    |        vids         
------------+-------------+---------------------
 葵つかさ   | Alice Japan | {DV-1195}
 葵つかさ   | S1          | {SNIS–436,SSIS-974}
 あやみ旬果 | PRESTIGE    | {ABP-356,EDD-209}
 あやみ旬果 | S1          | {SSNI-203}
 小島みなみ | Alice Japan | {DV-1279}
 小島みなみ | S1          | {SSIS-911,SNIS-133}
 鈴村あいり | PRESTIGE    | {EDD-218,ABF-061}
(7 rows)

select *
  from s1212bak.contract;

 id | gal_id | com_id 
----+--------+--------
  8 |      5 |      1
(1 row)

select *
  from s1212bak.videos;

 id | cid |   vid   
----+-----+---------
 13 |   8 | DV-1368
 12 |   1 | DV-1368
(2 rows)

rollback;

結語

正規化其實不好做,但是若能在實作的過程中不斷思考,研究.相信能夠發揮出其功能.

感謝

在此除了要感謝各位的回應,還要感謝 小島みなみ, 葵つかさ, あやみ旬果, 鈴村あいり 還有今天加入的 奧田咲.