Modeling with Document Databases: 5 Key Patterns

Document databases are more flexible in many ways than relational databases and this presents both opportunities and challenges. Poorly designed document structures adversely affect performance, increase maintenance overhead, and lead to unnecessarily complex application code. This presentation describes 5 commonly used design patterns in document databases: one-to-many, many-to-many, simple table inheritance, trees and lookup patterns. Each pattern is described in sufficient detail so attendees will understand their purpose, advantages, disadvantages and when to use them. Examples are drawn from multiple application areas. The talk will also include comparisons to relational database patterns for the benefit of relational modelers who are beginning to work with NoSQL databases.

Transcript

1. Modeling with Document Databases: 5 Key Patterns Dan Sullivan, Principal DS Applied Technologies Enterprise Data World 2015 Washington, D.C. April 1, 2015
2. My Background u  Data Architect / Engineer u  NoSQL and relational data modeler u  Big data u  Analytics, machine learning and text mining u  Cloud computing u  Computational Biologist u  Author u  No SQL for Mere Mortals u  Contributor to TechTarget
3. Overview u  Patterns and Data Modeling u  5 Key Patterns u  Anti-Patterns to Avoid u  Last suggestions { -------------- -------------- -------------- { ------ ------ } } [ ----------- ] } Document { -------------- -------------- -------------- { ------ ------ } } [ ----------- ] } Document { -------------- -------------- -------------- { ------ ------ } } [ ----------- ] } Document { -------------- -------------- -------------- { ------ ------ } } [ ----------- ] } Document
4. Patterns and Data Modeling
5. Schema-less <> Model-less u  Schema-less Document Databases u  No fixed schema u  Polymorphic documents u  ...however, not a Design Free for All u  Queries drives organization u  Performance Considerations u  Long-term Maintenance u  Middle Ground: Data Model Patterns u  Reusable methods for organizing data u  Model is implicit in document structures
6. Patterns u  Commonly used structure and organization u  Abstraction, not implementation specific u  Popularized by “Gang of Four” u  Applied to Relational Databases by David C. Hay u  Applies to NoSQL
7. Patterns u  Pattern 1: One-to-Many u  Pattern 2: Many-to-Many u  Pattern 3: Trees with References u  Pattern 4: Trees with Materialized Views u  Pattern 5: Entity Aggregation
8. Pattern 1: One-to-Many u  Embed Documents u  Multiple documents embedded u  “Many” attributes stored with “One” document u  Pros u  Single fetch returns primary and related data u  Might improve performance u  Simplifies application code u  Cons u  Increases document size u  Might degrade performance { OrderID: 1837373, customer : {Name: 'Jane Lox' Addr: '123 Main St' City: 'Boston' State: 'MA'}, orderItem:{ Sku: 38383838, Descr: 'Black chair'}, orderItem:{ Sku: 2872636, Descr: 'Glass desk'}, orderItem:{ Sku: 4747433, Descr: 'USB Drive 32GB''} }
9. One-to-Many Considerations u  Query attributes in embedded documents? u  Support for indexing embedded documents? u  Potential for arbitrary growth after record created? u  Need for atomic writes?
10. Patterns u  Pattern 1: One-to-Many u  Pattern 2: Many-to-Many u  Pattern 3: Trees with References u  Pattern 4: Trees with Materialized Views u  Pattern 5: Entity Aggregation
11. Pattern 2: Many-to-Many Employees ({empID: 1783, pname: “Michelle”, lname:”Jones” projects: [487,973, 287]} {empID: 9872, pname: “Bob”, lname:”Williams” projects: [487,973, 121]}) Projects ({projID:121, projName:'NoSQL Pilot'', team: [9872, 2431, {projID:487, projName:'Customer Churn Analysis'', team: [1873,9872]}) u References u  Minimizes redundancy u  Preserves integrity u  Reduces document growth u  Requires multiple reads
12. Pattern 2: Many-to-Many Employee {empID: 1783, pname: “Michelle”, lname:”Jones” projects: [ {projID:121, projName:'NoSQL Pilot''}, {projID:487, projName:'Customer Churn Analysis''} ]} Project {projID:121, projName:'NoSQL Pilot'', team: [ { empID: 1783, fname: “Michelle”, lname:”Jones”}, { empID: 9872, fname: “Bob”, lname:”Williams”} ]} u Embedded Documents u  Captures point in time data u  One document read retrieves data u  Increases document growth
13. Many-to-Many Considerations u  References u  Minimizes redundancy u  Preserves integrity u  Reduces document growth u  Requires multiple reads u  Embedded Documents u  Captures point in time data u  One document read retrieves data u  Increases document growth
14. Patterns u  Pattern 1: One-to-Many u  Pattern 2: Many-to-Many u  Pattern 3: Trees with References u  Pattern 4: Trees with Materialized Views u  Pattern 5: Entity Aggregation
15. Pattern 3: Trees with Parent & Child References u  Trees u  Single root document u  At most one parent u  No cycles u  Multiple Types u  Is-A u  Part-of
16. Pattern 3: Trees with References Children Refs. ({orgUnitID:178, orgUnitType: “Primary”, orgUnitName:”P1” children: [179,180]}, {orgUnitID:179, orgUnitType: “Branch”, orgUnitName:”B1” children: [181,182]}, {orgUnitID:180, orgUnitType: “Branch”, orgUnitName:”B2” children: [183,184]}) Parent Refs. ({orgUnitID:178, orgUnitType: “Primary”, orgUnitName:”P1” parent: 177}, {orgUnitID:179, orgUnitType: “Branch”, orgUnitName:”B1” parent: 178}, {orgUnitID:180, orgUnitType: “Branch”, orgUnitName:”B2” parent: 178})
17. Tree Considerations u  Children reference allow for top-down navigation u  Parent references allow for- bottom up navigation u  Combination allow for bottom-up and top-down navigation u  Avoid large arrays u  Consider need for point in time data
18. Patterns u  Pattern 1: One-to-Many u  Pattern 2: Many-to-Many u  Pattern 3: Trees with References u  Pattern 4: Trees with Materialized Views u  Pattern 5: Entity Aggregation
19. Pattern 4: Trees with Materialized Paths u  Full path from document to root is represented in document u  Implement with arrays or string u  Especially useful in hierarchical queries, i.e. a type and all its subtypes
20. Materialized Paths {orderItemId: 1873, prodType: “Pens” prodHierarchy:[ “Product_Categories”, ”Office Supplies”, ”Writing Instruments”, ”Pens”] }
21. Materialized Paths Considerations u  Support for multi-key indexing of arrays u  Use of regular expressions for pattern matching when string is used u  Ability to utilize indexes when string representation
22. Patterns u  Pattern 1: One-to-Many u  Pattern 2: Many-to-Many u  Pattern 3: Trees with References u  Pattern 4: Trees with Materialized Views u  Pattern 5: Entity Aggregation
23. Pattern 5: Entity Aggregation u  Entities with sub-types u  Relational models use multiple tables u  Document models use varying embedded documents u  Source of polymorphism
24. Entity Aggregation Polymorphic Documents {concertID: 132, locDescr:'Small Bar', price:”$30.00”, performerName:”Rolling Stones”} {concertID: 133, locDescr: “PDX Jazz Festival” price:”$75.00” festivalStart: 15-Feb festivalEnd: 25-Feb}
25. Entity Aggregation Considerations u  Aggregation vs Separate Collections u  High level branching u  Low level branching
26. Anti-Patterns
27. Anti-Patterns u  Large arrays u  Significant growth in document size u  Fetching more data than needed u  Fear of data duplication u  Thinking SQL, using NoSQL u  Normalizing without need
28. Closing Remarks u  Consider, is it worth it to loose: u  SQL u  Multi-statement transactions u  Triggers u  Let queries drive model u  Consider full life-cycle u  Exploit polymorphism
29. Questions?