資料庫物件命名原則

2 comments
程式設計使用命名慣例及有意義的名稱,會讓程式容易閱讀,而資料庫設計也應該如此。如果你希望自己的系統具可維護性(Maintainability),你就不應該草率的命名你的資料庫物件。採用命名原則的目的,是為了能夠讓資料庫物件名稱擁有足夠資訊,讓自己或是其他開發成員能清楚知道物件的類型及其使用目的。

本文將針對以下資料庫物件來說明:
  1. 資料表(Tables)
  2. 資料欄位(Columns)
  3. 檢視表(Views)
  4. 預儲程序(Stored Procedures)
  5. 觸發程序(Triggers)

所有的資料庫物件命名都應該遵循以下規則:
  1. 若未超過長度,限制應避免使用縮寫。
  2. 使用 Pascal 大小寫命名法,前綴詞則使用 Camel 命名法。
  3. 使用英文字母或底線字元組合,數字應盡量避免。
  4. 不要以底線字元當字首

若因長度的限制而需要使用縮寫時,應遵從以下的規則:
  1. 縮寫的第一個字元應該與單詞的第一個字母相同。
  2. 兩個字母的縮略字使用全大寫字母,而三個以上之字元的縮略字則使用 Pascal 大小寫慣例。遵守命名慣例的範例:ID、DB、Sql。違反命名慣例的範例:Id、Db、SQL。
  3. 不要使用任何不被廣泛接受的縮略字。

資料表(Tables)
資料表應該使用複數名稱,例如:Employees。若名稱由多個單詞組成,則只有最後一個單詞使用複數,例如:UserRoles。

如果資料表是屬於多對多關聯性(Many-To-Many Relationship)的聯合資料表(Junction Table),也就是該資料表的主索引鍵是由由資料表 A 和資料表 B 兩者的外部索引鍵所組成,就要連結資料表 A 和資料表 B 的名稱來命名。例如,Authors 資料表和 Titles 資料表之間具有多對多關聯性,而你需要一個資料表來維持這兩個資料表彼此之間的多關聯性,則這個資料表就應該命名為 TitlesAuthors。

資料欄位(Columns)
因為資料欄位是資料表的成員,所以欄位名稱不應再列入冗餘的資料表名稱。

檢視表(Views)
檢視表應比照資料表命名規則,但為有別於資料表,應加入前綴詞以避免混淆,例如:vw_Users。

預儲程序(Stored Procedures)
{Table Name}_{Action}[Qualifier]

  • Table Name:預儲程序主要作用的資料表名稱。如果沒有作用在資料表上,就使用 Util(Utility的簡寫)來命名。
  • Action:這部份指的是作用到資料表的主要動作,例如:Create、Get、Update、Delete。如果是 Util 程序,這個部份就不一定要加上。
  • Qualifier:可以選擇性用來更清楚描述這個預儲程序要進行的動作。

觸發程序(Triggers)
{Prefix}_{Table Name}_{Actions Covered}
  • Prefix:使用 trg_ 做為前綴詞,以別於其他資料庫物件。
  • Table Name:執行觸發程序所在的資料表名稱。
  • Actions Covered: 啟動觸發程序的 Insert、Update、Delete 選項。在此用 "Ins"、"Upd"、"Del" 來描述。


參考文章:
Database Object Naming Rules by sbates
DevCampus Database Naming Conventions by Jason Mauss
Database object naming conventions by Vyas

繼續閱讀...

定序衝突 (Transact-SQL)

1 comments
當你的 SQL 查詢試圖去比較不同定序的資料欄位時,就會出現如下類似的錯誤訊息:
訊息 468,層級 16,狀態 9,行 1
無法解析 equal to 作業中 "Latin1_General_CI_AI" 與 "Chinese_Taiwan_Stroke_CI_AS" 之間的定序衝突。

以下是在 SQL Server 2005 所引發錯誤的 SQL 查詢範例:
select * from sysobjects o
left join ::fn_Listextendedproperty(null, N'user',N'dbo',N'table', default, null, null) e on o.name = e.objname
where type = 'U'

當你在定序為 Chinese_Taiwan_Stroke_CI_AS 的資料庫,使用 fn_Listextendedproperty 函式與 sysobjects 資料表合併查詢時,就會發生定序衝突(Collation Conflict)的錯誤。這是因為 fn_Listextendedproperty 函式回傳的資料固定是以 Latin1_General_CI_AI 為定序,所以導致定序不一致的情況。

上例的解決作法就是在運算式中,做明確的字串定序轉換,如以下範例:
select * from sysobjects o
left join ::fn_Listextendedproperty(null, N'user',N'dbo',N'table', default, null, null) e on o.name = e.objname COLLATE Chinese_Taiwan_Stroke_CI_AS
where type = 'U'

你也可以利用 COLLATE 子句中的 database_default 選項,指定特定的資料行使用目前連接的使用者資料庫之定序預設值。

繼續閱讀...

為你的網站部署 P3P 隱私權政策

1 comments
P3P 是由全球資訊網協會(W3C,World Wide Web Consortium)所發展出的隱私權偏好平台(Platform for Privacy Preferences),它一種標準通訊協定及結構,Web 伺服器可以使用 P3P,清楚的描述網站的資料收集及隱私權原則。當你瀏覽網站時,具備 P3P 功能的瀏覽器會判斷你的隱私權偏好選項是否與網站的隱私權原則和使用者的隱私權配置相符。而 Internet Explorer 6 就是第一個支援 P3P 標準的瀏覽器,他提供六種預設的使用者隱私權設定,從接受所有 cookie 到封鎖所有 cookie 都有。在預設的情況下,當 Web 伺服器的隱私權原則和使用者的隱私權配置不一樣時,IE 會選擇性地封鎖不同類型的 cookie,並針對不符合你的隱私權設定的網站向你發出警示。


要部署 P3P 前,必須先建立三個檔案:
  1. 描述隱私權保護政策的 HTML 網頁,其檔名為 policy.html。
  2. 完整的 P3P 隱私權原則檔,其檔名為 policy.xml。
  3. 原則參考檔,其檔名為 p3p.xml。
首先要把給 HTML 格式的隱私權保護政策網頁的內容依據 P3P specification 來建立 XML 格式的 P3P 隱私權原則檔案;但 P3P 規格非常複雜,強烈建議使用 P3P 編輯工具,如:
  1. IBM P3P Policy Editor
  2. PrivacyBot.com
  3. P3PEdit
這些編輯器會協助你產生 policy.xml、p3p.xml,以下是 policy.xml 的範例:
<POLICY xmlns="http://www.w3.org/2000/12/p3pv1"
discuri="http://網域名稱/policy.html"
opturi="http://網域名稱/policy.html">
<ENTITY>
<DATA-GROUP>
<DATA ref="#business.name">公司名稱</DATA>
<DATA ref="#business.contact-info.postal.street">地址</DATA>
<DATA ref="#business.contact-info.postal.city">城市</DATA>
<DATA ref="#business.contact-info.postal.postalcode">郵遞區號</DATA>
<DATA ref="#business.contact-info.postal.country">國家</DATA>
<DATA ref="#business.contact-info.online.email">電子郵件</DATA>
<DATA ref="#business.contact-info.telecom.telephone.number">電話號碼</DATA>
</DATA-GROUP>
</ENTITY>
<ACCESS><nonident/></ACCESS>
<STATEMENT>
<PURPOSE><admin/><develop/></PURPOSE>
<RECIPIENT><ours/></RECIPIENT>
<RETENTION><stated-purpose/></RETENTION>
<DATA-GROUP>
<DATA ref="#dynamic.clickstream.server"/>
<DATA ref="#dynamic.http.useragent"/>
</DATA-GROUP>
</STATEMENT>
<STATEMENT>
<PURPOSE><pseudo-analysis required="opt-in"/></PURPOSE>
<RECIPIENT><other-recipient/></RECIPIENT>
<RETENTION><indefinitely/></RETENTION>
<DATA-GROUP>
<DATA ref="#user.home-info.postal.postalcode">
<CATEGORIES><demographic/></CATEGORIES>
</DATA>
</DATA-GROUP>
</STATEMENT>
</POLICY>

根據上面的文件內容,再轉成「P3P 精簡原則」(P3P compact policy)代碼:
NOI ADM DEV PSAi COM NAV OUR STP IND DEM

例如:在 ACCESS 元素包含的文字 <nonident/> 及 CATEGORIES 元素包含的文字 <demographic/> 分別表示為 NOI、DEM 。如果你是使用編輯器,這部份當然就不用操心了,因為編輯器會自動產生給你。這些精簡原則代碼所代表的意義如下:
  1. NOI 未收集個人可識別資訊。
  2. ADM 資料是為了網站管理而被使用。
  3. DEV 資料是為了研究而被使用。
  4. PSAi 非已識別的相關資訊將被用來做研究、分析及報告。例如某個郵遞區號的使用者人數。
  5. COM 電腦資訊將被收集。
  6. NAV 導覽與點擊串流資料將被收集。
  7. OUR 資料只給我們自己與我們的代理者使用。
  8. STP 資料是按收集目的被保留。
  9. IND 資料被保留的期限沒有明確指定。
  10. DEM 個人特徵資料將會被收集,例如性別、年齡及收入。
最後是 p3p.xml 檔,它必須參照到完整的隱私權原則:
<META xmlns="http://www.w3.org/2000/12/p3pv1">
<POLICY-REFERENCES>
<POLICY-REF about="policy.xml">
<INCLUDE>\*</INCLUDE>
<COOKIE-INCLUDE name="*" value="*" domain="*" path="*"/>
</POLICY-REF>
</POLICY-REFERENCES>
</META>

其中, POLICY-REF 元素的 about 屬性就是指向 policy.xml ,這也正是 p3p.xml 的目的。

接下來就是部署 P3P 到你的 Web 伺服器:
  1. 將原則參考(policy-reference)檔案 p3p.xml 部署到 /w3c/p3p.xml。
  2. 將完整的隱私權原則檔案部署到相同目錄,例如:/w3c/policy.xml。
  3. 將原則摘要加入 HTTP 標頭。

使用 HTTP 標頭
以網路資訊服務(IIS)管理員為例:
  1. 開啟網路資訊服務(IIS)管理員。
  2. 開啟網站內容視窗。
  3. 點選[HTTP 標頭]頁簽。
  4. 在[自訂 HTTP 標頭]處,按下[新增]按鈕。
  5. 在[自訂標頭名稱]欄位輸入 P3P
  6. 在[自訂標頭值]欄位輸入隱私權摘要字串,例如:CP="NOI ADM DEV PSAi COM NAV OUR STP IND DEM"
  7. 按下[確定],並離開資訊服務(IIS)管理員。
除了在 IIS 使用 HTTP 標頭外,你也可以寫程式輸出自訂標頭來達到相同的目的。例如:在 ASP.NET 可以透過 global.asax 撰寫下列程式碼:
protected void Application_BeginRequest(Object sender, EventArgs e)
{
HttpContext.Current.Response.AddHeader("p3p",
"CP=\"NOI ADM DEV PSAi COM NAV OUR STP IND DEM\"");
}

部署成功後,就可以打開瀏覽器檢視你的網站的隱私權政策,如果是使用 IE6 的話,請在[檢視]功能表,按一下[隱私權報告],就可以看到隱私權摘要報告。


參考資料:
How to Deploy P3P Privacy Policies on Your Web Site by Microsoft
The Platform for Privacy Preferences 1.0 Deployment Guide by W3C

繼續閱讀...

Crystal Reports - 設計 Master / Detail 報表

9 comments
本文使用 Crystal Reports XI 以簡單的出貨報表為範例,建立顯示訂單主檔的主報表及訂單明細檔的子報表,然後使用訂單編號當作參數,來連結這兩個報表。

建立子報表(訂單明細)
1.加入 SQL 查詢命令至報表:
SELECT OrderDetails.OrderNumber,OrderDetails.ProductID,
Products.Name,OrderDetails.UnitPrice,OrderDetails.Quantity,OrderDetails.ExtendedPrice FROM OrderDetails
INNER JOIN Products ON OrderDetails.ProductID=Products.ID
WHERE OrderDetails.OrderNumber={?訂單編號}
在命令中我加入了查詢參數{?訂單編號},所以必須在命令視窗右邊的參數清單建立名為訂單編號的命令參數,查詢參數才會有效。


2.在報表明細區段插入資料庫欄位後,將報表儲存為[訂單明細檔.rpt]。

建立主報表(訂單主檔)
1.加入 SQL 查詢命令至報表:
SELECT Orders.OrderNumber,Orders.ShipName,Orders.ShipCity,Orders.ShipPostalCode,
Orders.ShipAddress,Orders.OrderDate,Orders.ShippedDate
FROM Orders
WHERE Orders.OrderNumber={?訂單編號}
在命令中同樣也加入查詢參數{?訂單編號},所以也要在參數清單建立名為訂單編號的命令參數。

2.將訂單主檔的資料欄位插入頁首區段。

3.接著在訂單主檔下方插入子報表:[訂單明細檔.rpt]。

4.在主報表中點選[訂單明細檔.rpt],在滑鼠右鍵功能選單中選擇[變更子報表連結]。

5.在[子報表連結]視窗中,在主報表的[可使用的欄位]清單中,將[?訂單編號]加入[要連結的欄位]。然後,在[要使用的子報表參數欄位],選擇我們在子報表建立的[?訂單編號]參數,如此一來便會達到主報表與子報表共用參數的目的。完成後,按下確定關閉視窗。


6.最後將報表儲存,報表就完成了。

繼續閱讀...