iT邦幫忙

2019 iT 邦幫忙鐵人賽

DAY 11
1

由於開立資料庫相關資料表時,都必須將資料庫正規化。所以主要的表單上,通常都只存 id,然後再利用 JOIN 語法取得關聯檔案的 name,舉個實際的範例來說,在訂單主檔 zen_order2_m 中有紀錄下面4個關聯主檔。

cid 客戶編號(關聯 zen_customer.cid)
pid 業務代號(關聯 zen_person.pid) 
did 所屬部門代號(關聯 zen_department.did)
wid 倉庫代號(關聯 zen_warehouse.wid)

但是光看 id,不一定能知道它的名稱內容,此時我們就會利用 left join 語法取得 name 的內容,但是實際在開發系統時,考慮執行效能的 issue 時,有一個天條,這是菜鳥常會忽視的致命問題,就是「要想盡辦法降低對資料庫的存取動作及次數」,Why? 問題不難,請您自己想想就會了解。

當開啟訂單這個表單時,以上面的範例來說,您可以先取得訂單

select * from zen_order2_m where billno = @billno

然後分四次取回相關的名稱欄位

select cname from zen_customer where cid = @cid;
select pname from zen_person where pid = @pid;
select dname from zen_department where did = @did;
select wname from zen_warehouse where wid = @wid;

也可以在取得訂單資料時,一次取回。

select m.*,c.cname,p.pname,d.dname,w.wname from zen_order2_m m 
left join zen_customer c on m.cid = c.cid
left join zen_person p on m.pid = p.pid
left join zen_department d on m.did = d.did
left join zen_warehouse w on m.wid = w.wid

上面的 sql 語法如果會被頻繁使用,也可以寫成 View,如下

create view vOrder2_m
as
select m.*,c.cname,p.pname,d.dname,w.wname from zen_order2_m m 
left join zen_customer c on m.cid = c.cid
left join zen_person p on m.pid = p.pid
left join zen_department d on m.did = d.did
left join zen_warehouse w on m.wid = w.wid

View 很像 Table,也可以直接用 select 取得資料

select * from vOrder2_m

在系統操作的過程中,有2個系統資料表會非常頻繁的被存取,所以為了提高資料庫的存取效率,這2個 Table 通常會cache在local端,以降低對資料庫的存取次數。
keyvalue (代碼設定表)

系統中會有很多 combo 下拉元件,要能定義下拉的 id 及其顯示內容。此時可以將設定值設定在此 Table 中,程式可以讀取內容後就可以配合操作,通常為了統一程式碼的規範,keytag 通常是該下拉欄位的欄位名稱,而記錄的值是 keyid,keyvalue 是看到的選擇內容。

https://ithelp.ithome.com.tw/upload/images/20181019/20111421iSIK5y4E4g.png

parameter (參數設定表)

為了讓系統更有彈性,很多程式碼會依不同的設定值,執行不同的顯示或運算程式
在本後台系統中 parameter 就是存放各式各樣的參數設定值,有了這個 Table就可以在開發過程,甚至上線使用後,依實際的需求增加參數,就可以讓系統有更大的彈性。
下圖整理了一些 CRM 的參數供參考

https://ithelp.ithome.com.tw/upload/images/20181019/20111421fPqar7nuV5.png

至於如何及何時 Cache 到本機,這個規格可以視實際狀況調整,也不只限於這2個 Table,只要是常被存取的資料,且異動佈頻繁的 Table,就可以考慮套用本機制。

當然,在開發系統時,權限(Security) 也是非常重要的重點,權限基本上可以全部設定在資料庫中,透過幾支系統表單設定,就可以賦予不同的 user 不同的功能權限,甚至可以限制查詢的資料內容。在本系列文最開始時,有使用一個 sys_users 這個登入帳號的 Table,這個 Table 就是 Security 架構的一部分。

由於權限設定是必要的基礎功能,請在開發系統前期就應該考慮好,本系列文限於篇幅無法詳細說明,不過開始習慣我的風格,讀者應該會猜出來我都是寫成 Store Procedure。然後都包在開發架構的底層,所以前端程式通常都是包成共用程式(通常是 js),即可控制表單的相關功能。

在這個單元,我拉哩啦咂的介紹了幾個要點,都是在初期規劃系統時就應該先考慮好,如果沒有規劃好就直接開發系統,後果只有四個字「後患無窮」。今天的內容就到這,感謝您的收看,我們明天見。


上一篇
Day10:範例 db Table Schema 及測試用 store procedure 的說明。
下一篇
Day12:常用的資料庫資料型態
系列文
以資料庫為開發核心,利用通用 API 玩轉後端資料存取的概念與實作30

尚未有邦友留言

立即登入留言