最近打算寫寫資料庫建模的文章，所以打算分析微軟官方提供的SQL Server示例資料庫AdventureWorks，看看這個資料庫中有哪些值得學習的地方。
首先我們需要下載安裝一個SQL Server資料庫引擎，然後下載示例資料庫，這裡筆者用的是SQL2008R2，所以下載的是AdventureWorks2008R2，下載地址：
http://msftdbprodsamples.codeplex.com/
下載資料庫後附加到SQL Server中即可看到這個資料庫。
這是一個自行車製造和銷售公司的資料庫，該公司建立自己的銷售網站，提供線上銷售。首先看看這個資料庫的結構，其建立了多個Schema，通過Schema來劃分表所在的模組，比如HumanResources，Person，Production，Purchasing和Sales。如果是非常通用的表，比如日誌表，那麼就不屬於任何模組，使用系統預設的Schema：dbo。
對於這麼一個複雜的模型，我們可以按照：主要實體、附屬實體、事務實體關聯關係的順序進行分析。
主要實體
對於整個系統來說，BusinessEntity是最核心的實體，用於表示一個“人”，這裡的人是打引號的，因為它既可以表示真實的自然人，也可以表示：公司、組織甚至一個商店，可以認為是一個法人。對於這個資料庫模型來說，有3個實體繼承自BusinessEntity，那就是Person，Store，Vendor。
對於Person自然人來說，他可能是公司的員工，也可能是客戶，所以我們又關聯出了兩個實體Employee和Customer。這裡需要注意的是，在這個模型中，他並不把一個自然人標識為一個客戶，而是對不同的Store，會形成不同的客戶。也就是說對於公司來說，他並沒有客戶主資料，同一個人在不同的店消費，那麼就會在不同的店中記為一個客戶。為什麼要這麼設計，確實很奇怪，可能是業務上的需求吧。
這裡延伸到Employee，就可以把HumanResources下面的實體分析一下，很顯然Department是主要實體，至於Employee和Department之間的關係，我們接下來再分析，這裡我們只找主要實體。Employee如果在銷售部門，那麼就是一個SalesPerson，所以這個實體是繼承自Employee。另外在Production中還有一個很重要的實體Product，用於表示生產和銷售的產品。
附屬實體
所謂附屬實體，就是依附於主要實體而存在，對主實體的屬性進行補充的實體，如果主要實體不存在，那麼附屬實體裡面的資料就沒有意義。對於前面找的主要實體，我們一個一個的分析：
BusinessEntity
BusinessEntity有兩個附屬實體：BusinessEntityContact和BusinessEntityAddress，對於聯絡人實體，是和Person形成多對多關係，所以BusinessEntityContact是多對多產生的中間表，另外再加上ContactType說明聯絡人的型別。而對於業務實體的地址，系統也抽象出了一個Address表，使得BusinessEntity和Address之間形成多對多關係。
Person
對於Person表，關聯的表分為兩類，一類是一對多或多對多的普通關聯表，比如一個人有多個PersonPhone，一個人有多個EmailAddress，或者一個人持有多張信用卡PersonCreditCard。這裡把CreditCard和Person設定成多對多的關係，我想應該這裡的CreditCard包含公司商務卡的情況，這種卡的真正持有人是公司，但是公司會派發給Sales用，如果Sales離職了，那麼這張卡會收回，派發給其他的員工用，所以這就形成了多對多關係。另外一類是一對一的拆分或繼承關係，比如Password表。如果是簡單的設計，我們完全可以把Password相關欄位放在Person表中，而這裡卻獨立出來形成一對一關係，主要可能是以下幾方面的原因：
安全考慮：Password的內容很機密，獨立成表後可以單獨對這個表進行加密，許可權分配等。
效能考慮：Password的內容只用於登入系統時驗證，以後接下來的所有查詢都用不到這些欄位，所以不放在Person表中，系統在查詢Person表時就不需要連帶著把不需要的欄位查出來。
Employee
這裡主要涉及到的是HumanResources下的表，除了員工的基本資訊外還記錄了員工的履歷，工資變動，部門變動情況。一個Employee對應多個JobCandidate，為什麼是一對多關係呢？因為應聘者可以製作個人簡歷的多個版本，然後投公司的不同部門，最後如果應聘者被錄取了，那麼就可以把JobCandidate中的BusinessEntityID設定為Employee的ID，如果應聘失敗，那麼BusinessEntityID就是NULL。EmployeePayHistory是員工的工資表，但是不是發工資的記錄表，只是記錄員工的工資基本資訊，如果工資變動就建立一條新的記錄。Employee和Department是多對多的關係，並不是因為一個員工身兼數職，在多個部門同時幹活，而是因為要記錄員工的部門調動情況，所以保留了所有歷史記錄，形成了多對多關係。另外比普通公司的部門員工表不同，這個系統還有一個輪班表Shift，那是因為這是個製造業公司也有門店進行銷售，所以會分為早班，中班和晚班，一個員工的輪班是固定的，如果發生變化，比如以前是上夜班，現在改為上早班，那麼EmployeeDepartmentHistory中也會對應生成一條新的記錄。
Sales
銷售繼承至Employee，主要有銷售區域，銷售配額等附加的屬性。本身銷售區域和銷售配額可以看做是Sales表的屬性，但是為了記錄歷史，所以獨立出來了一對多的表：SalesTerritoryHistory和SalesPersonQuotaHistory。
這裡需要說明一下SalesTerritory表並不是Sales的附屬表，他本身是一個獨立的實體。
Product
這個實體應該是各個主實體中屬性最複雜的實體了。主要分為ProductModel和Product兩塊。
先說ProductModel，可以理解為樣品，樣機或者是模型，在進行量產前需要先生產ProductModel。對於ProductModel，主要有產品的部件關係圖Illustration和描述ProductDescription。ProductModel和Illustration是普通的多對多關係，一個模型有多個部件關係圖，一個部件關係圖也可以用於多個樣機中。而對於描述，除了普通的多對多關係外，還增加了一個多語言的關係。於是增加了Culture表，形成了三個表的多對多關係。實際上這種多語言模型並不好，很容易產生錯誤，對於多語言的處理，可以建立更好的模型。
接下來就是Product實體，可以將相關的表分為三類：
多對一：產品的分類Category和前面提到的ProductModel。
一對多：產品成本歷史ProductCostHistory，產品的組成BillOfMaterials，產品的庫存ProductInventory，產品價格歷史ProductListPriceHistory，產品的複查ProductReview。
多對多：產品文件ProductDocument和產品照片ProductPhoto。
產品分類沒啥好說的，就是普通的二級分類法，一級大分類在ProductCategory，二級小分類在ProductSubcategory，然後所有產品都必須歸屬到二級小分類上。ProductCostHistory和ProductListPriceHistory都是因為要記錄基於時間段的歷史而形成的一對多關係，其中必有StartDate和EndDate來劃分時間區間。【歷史資料記錄】
關於產品文件和產品照片，由於存在複用的情況（比如產品的外觀是一模一樣的，只是某些內部引數不一樣，那麼產品照片就可以複用。）所以就形成了多對多關係，有多對多關係就會有中間表。產品圖片由於會有細節照片，各個角度的照片，所以在多對多關係表中另外定義了一個Primary欄位用於說明當前選用的照片是不是主體照片。
事務實體
前面分析的實體都是在主謂賓語句中當主語的物件，接下來我們要分析這些主語之間發生關聯，進行事務操作後產生的賓語物件。
對於SalesPerson、Product、Customer在一起時，聯想到的就是銷售訂單：
SalesOrder
只要是涉及表單的東西（銷售訂單、報銷單、採購訂單、發貨單等）大部分情況都會分為Header和ItemDetail兩個表，在銷售訂單中Header用於記錄單據的銷售的人員，客戶，總金額等資訊，而ItemDetail中記錄了具體銷售的產品，數量等資訊。
下面先分析Header：
Header建立了SalesPerson與Customer的聯絡，另外還有正規化化的一些欄位，比如ShipToAddress，BillToAddress，ShipMethod等。除了這幾個實體外，我們需要單獨分析一下以下幾個實體：
Territory，這是在前面介紹Sales的時候說到，這個銷售區域和SalesPerson是有關聯的，按理來說，Header表已經關聯了SalesPerson表，我們就可以通過SalesPerson獲得其下單時對應的Territory，為什麼還需要額外新增這個Header到Territory的直接關係呢？這是出於效能的考慮而增加的冗餘，對於有時效性的物件，最好是直接關聯，而不是通過中間物件jion多個表去關聯。讓我們看看如果沒有直接關聯Territory，那麼我們的查詢到底有多複雜：
select h.*,st.*
from Sales.SalesOrderHeader h
left join Sales.SalesPerson sp
on h.SalesPersonID=sp.BusinessEntityID
left join Sales.SalesTerritoryHistory sth
on sp.BusinessEntityID=sth.BusinessEntityID and h.OrderDate between sth.StartDate and sth.EndDate
left join Sales.SalesTerritory st
on sth.TerritoryID=st.TerritoryID
下面再來看看幣種和匯率的相關表Currency。在這個系統中，Header並沒有直接說明用什麼幣種付款，什麼幣種結算，匯率是多少，而是把這幾個欄位放在CurrencyRate表中，通過引用CurrencyRate來表示。雖然說獨立出來後沒有直接放在Header表中直觀，不過減少了冗餘，只需要做一次Join就能拿到結果，所以效能上還是能接受的。【雖然從關係上需要Join了CurrencyRate後再JoinCurrency表才能完整，但是一般來說Currency表只用於CurrencyRate的限定，而不需要在查詢時使用Currency表，因為CurrencyCode是國際標準編碼，只需要顯示Code就夠了。】
Header和SalesReason是多對多關係，在客戶下單的時候讓使用者複選購買原因，是因為促銷，還是看了雜誌廣告之類的，簡單多對多關係，這個沒啥好說的。
SalesOrderDetail
Header和OrderDetail是一對多關係，Detail記錄了具體購買了啥產品，購買單價，數量等，所以關聯的是Product，但是在這個系統中，他並不是直接關聯Product物件，而是在之間建立了SpecialOfferProduct，該表是Product和SpecialOffer的多對多中間表。
為什麼會這麼做呢？這主要是跟具體的業務相關。產品在生產出來以後有一個標價Product.ListPrice，但是在實際銷售中，商家會有各種促銷活動（比如買10個以上9.8折，25個以上9折等），所以會形成Product和SpecialOffer的多對多關係，維護了哪些產品能夠有哪些折扣。為了統一模型，如果產品不做任何打折促銷，也會在SpecialOffer中維護一條記錄“No Discount”。
這裡有一個特別的技巧，SpecialOfferProduct是沒有自己獨立的主鍵的，而是使用ProductId和SpecialOfferId作為聯合主鍵，然後在OrderDetail引用具體的SpecialOfferProduct時，就會將ProductId和SpecialOfferId引用到其列中。所以在模型上來說，是OrderDetail關聯SpecialOfferProduct，然後再關聯Product，但是我們在實際查詢中，完全可以忽略SpecialOfferProduct表，直接用OrderDetail去Join Product即可，所以效能上沒有任何影響，這是一個漂亮的設計。
而當Employee、Product和Vendor在一起時，聯想到的就是採購訂單：
PurchaseOrder
和銷售訂單類似，採購訂單也 分為PurchaseOrderHeader和PurchaseOrderDetail。
系統中先使用ProductVendor定義了哪些Vendor能供應哪些產品，在生成採購訂單時會基於這裡面的內容來生成，但是在模型上並不直接反應，因為Product屬於Detail表，而Vendor是屬於Header表，不能像前面說到的SpecialOfferProduct一樣通過引用來傳遞這種限制。
Header記錄的是採購人員Employee與供應商Vendor的關係。一個採購訂單Header中會包含多個明細Detail，裡面記錄了採購哪些Product。採購訂單比銷售訂單簡單很多，最為買方，不會去記錄促銷，購買原因之類的資訊。另外採購中沒有涉及到幣種匯率問題，我估計這是因為產品都在國內採購和結算，所以只有一種幣種，而銷售是面向世界各地，所以涉及到幣種匯率。
WorkOrder
除了前面說到的銷售訂單和採購訂單外，在生產過程中還有生產訂單，用於表示產品的生產情況。主要有WorkOrder和WorkOrderRouting兩個實體。
WorkOrder記錄了生產某個產品的數量、報廢和時間情況，而WorkOrderRouting記錄的是在某個產品的具體生產過程中有哪些工序，每個工序的時間、成本等情況。總的來說，這是一個非常非常簡化的生產工作訂單模型。
其他實體
除了前面說到的實體外，還有其他幾個獨立出來的實體需要說明一下：
TransactionHistory
另外還有一個其歸檔表TransactionHistoryArchive，其結構和TransactionHistory一模一樣，這裡面記錄的是生產工作訂單或者採購訂單或者銷售訂單這3個事務的產品、日期，數量等公共資訊。這個表可以認為是一個事務的日誌表，平時並不參與各個實體的查詢，只有在審計或者跟蹤資料變化時才用到。
TransactionHistory表中的資料是在各個Order表上建立Trigger自動插入進去的，而不是由外部程式程式碼去控制。由於本身事務表的資料量就比較大，而這個表卻存了三個事務表中的資料，所以增長特別快，必須進行歸檔操作，把老資料搬移到另一個歸檔表中，這樣才能保證查詢新TransactionHistory表的速度。
AWBuildVersion
這是一個記錄當前資料庫定義建立時資料庫的版本也可以定義當前資料庫定義指令碼的版本。對於通用的產品來說，這個表比較重要，因為產品可能需要升級，升級程式在升級前讀取這個表，知道了當前資料庫定義是什麼個版本，然後就可以查詢到將當前版本的資料庫升級到新版的資料庫所需要修改的SQL，然後執行這些SQL。
而應用程式在執行時第一件事就是檢查這個表中的版本資訊，保證資料庫定義的版本與程式要求的版本匹配，這樣程式才能正常執行。
對於企業內部系統，一般只有一個例項，而且由企業內部的IT人員開發維護，所以這個表沒有也沒什麼問題。
DatabaseLog
這是記錄資料庫DDL（資料定義語言，比如CREATE， ALTER， DROP等）操作的日誌表。這個表是由Database Trigger自動維護，當在這個資料庫中執行了DDL的時候，系統會觸發Trigger，往這個表中記錄一條資料。這是一個好東西！
另外還有一些因為正規化化抽象出來的碼錶，我在前面的模型中沒有提到，比如CountryRegion，StateProvince等這些都比較簡單，就不一一累述了。
這篇文章我只是簡單分析了下實體和實體關係，下面一篇文章會進一步分析其中的細節，有哪些優缺點。