Denormalization. - Causes redundancy, but fast performance & no referential integrity - Denormalize when • specific queries occur frequently, • a strict performance is required and • it is not heavily updated
- Causes redundancy, but fast performance & no referential integrity
- Denormalize when
• specific queries occur frequently,
• a strict performance is required and
• it is not heavily updated
-So, denormalize only when there is a very clear advantage to doing so and document carefully the reason for doing so
EMP (E#, Ename)
SKILL (E#, Skill)
Use EMP (E#, Skill, Ename) when Emp has a smaller # of attributes.
- This means use Method 2 of 1NF algorithm. But know the danger of this method as we discussed in MVD.
(2) Embed stable Code-Interpretation (Reference) Table.
FLIGHT (F#, Departs, From_Code, To_Code)
CODE (Code, Airport_Name)
Use FLIGHT (F#, Departs, From_AP, From_Code, To_AP, To_Code)
Combine1:1 or 1:N (a) when N is small and (b) the record on the "one" side is small (thus the amount of redundancy will be small)
SALE (S#, SPName, SaleDate), SALE_ITEMS (S#, Line#, Code, Qty)
SALE(S#, Line#, SPName, SaleDate, Code, Qty)
-- "How many T179's did we sell yeaterday?" can be answered without join.
Order_Item(O#, I#, C#, Cname, I_Desc, Qty, I_Price)
(4) When the other entity in is not interesting by itself
Order(O#, ODate, OShipTerms, PmtTerms, Cname, CAddr)
(5) Replicate non-frequently updated attributes to avoid JOIN
WORK_ON (ESSN, P_NUM, PName, Hours)
- Add summary attributes or derived attributes
- Redundant relationships can improve performance with the cost of update overhead
ZIP --> CITY
- Most frequently used attributes
- Prefer small sized attributes (used in indexes, Ref. integrity)
- Create a set of appropriate indexes optimzing queries (This will be discussed more in physical DB chapters.)