Database Normalization 設計問題

本帖最後由 manpaul 於 2013-5-18 03:05 編輯

在設計關於Customer Order Databse Normalization時,遇上以上問題:
1. 會否對於Derived Attribute開一個Field來儲存其資料呢?(因應咩情況而用)
2. 2NF與3NF的關係是什麼?
3. 假如 Customer may have optionally membership, A Customer can followed by the consultant.
MemberID is unique key? 如果係又如何在Normailization表達呢?
3NF
Order (OrderID, CustomerID, EmployeeID, InvoiceID)
OrderProduct (OrderID, ProductID, Quantity, Integration, Amount)
Product (ProductID, ProductName, UnitPrice, DiscountPrice)
Customer (CustomerID, CustomerName, CustomerPhoneNo, CustomerAddress, MemberID)
Member (MemberID, MemberLastIntegration, MemberIntegrationGained, MemberIntegrationUsed, MemberLatestIntegration)
Employee (EmployeeID, EmployeeName, EmployeePhoneNo, CustomerID)
唔知呢個數據庫是否岩要設計的情況?
4. 我見有d Order / Invoice 例子,OrderNO / InvoiceNO Primary Key is "A0123456",應否使用這種設計?

本帖最後由 onc999 於 2013-5-18 06:53 編輯

試下答些少: 如一EMPLOYEE(CONSULTANT)會FOLLOWUP超過一個CUSTOMER的話,EMPLOYEE TABLE 應改為: (EmployeeID, EmployeeName, EmployeePhoneNo) , 然後加一個「FOLLOWUP」TABLE: Followup (EmployeeID, CustomerID)   這樣一個CUSTOMER可由幾個EMPLOYEE FOLLOWUP,一個EMPLOYEE 亦可FOLLOWUP 多個CUSTOMER。

TOP

Normal form 等其他人答
normalization 同 table 數量會成正比
都係跟實際情況衡量好D

Q4, 個人意見係用 id (surrogate key) 做 pk 好過直接用 actual data 做 pk
因為有時都要應付一d人為錯誤, 修改 data 點都比修改 primary key 容易 d

TOP

本帖最後由 神秘二代 於 2013-5-20 17:41 編輯

http://tw.myblog.yahoo.com/jack0 ... =12284&prev=...

TOP