Relational vs. Document DBs - How do I decide?

This presentation will recap the design considerations developers prioritize in picking one technology over the other. Using SQL Server and MongoDB as representative systems, we will consider the developer experience using these technologies. We will take a look at at the tools used to design, query, transform and optimize both relational and document database systems. Finally, we will take a brief look at Cosmos and the features it offers extending and simplifying the cognitive load a little bit more than Mongo.

This presentation is aimed at the developers who have been using either relational or document database technology and want to see what they were missing.

Transcript

1. Relational vs. Document Databases
   How do I decide?
   Tuesday November 28. 2017
   

   View Slide

2. Agenda
   A Little About Me
   

   View Slide

3. Agenda
   A Little About Me
   Recap of Relational Database Features
   Anatomy of a Claim
   Possible Claim Models
   

   View Slide

4. Agenda
   A Little About Me
   Recap of Relational Database Features
   Anatomy of a Claim
   Document Database Features
   Claims Document
   CosmosDB
   Possible Claim Models
   

   View Slide

5. Agenda
   A Little About Me
   Recap of Relational Database Features
   Anatomy of a Claim
   Insights and Opinions
   Document Database Features
   Claims Document
   CosmosDB
   Possible Claim Models
   x
   

   View Slide

6. Doug Corbett
   Founder and Principal Consultant
   Lionheart Consultants
   dougccorbett@gmail.com
   

   View Slide

7. A Little About Me – The Early Days
   http://www.advanceddungeonsandparenting.com/2017/08/tom-moldvay-basic-why-its-great-and.html
   

   View Slide

8. A Little About Me – The Early Days
   • http://techland.time.com/2012/08/03/trs-80/
   http://www.advanceddungeonsandparenting.com/2017/08/tom-moldvay-basic-why-its-great-and.html
   

   View Slide

9. A Little About Me – The Early Days
   5th Grade Dungeon & Dragons Character Generator
   • http://techland.time.com/2012/08/03/trs-80/
   http://www.advanceddungeonsandparenting.com/2017/08/tom-moldvay-basic-why-its-great-and.html
   

   View Slide

10. A Little About Me – 80’s Aspirations
    • http://www.dailymail.co.uk/tvshowbiz/article-2842087/He-s-David-Spade-Joe-Dirt-mullet-big-filming-sequel-New-Orleans.html
    • http://www.mobygames.com/game/dos/wing-commander-iii-heart-of-the-tiger/screenshots/gameShotId,9865/
    

    View Slide

11. A Little About Me – Dose of Reality
    • https://en.wikipedia.org/wiki/Pong
    

    View Slide

12. A Little About Me – Dose of Reality
    • https://en.wikipedia.org/wiki/Pong • https://www.rottentomatoes.com/m/shrek_forever_after/
    

    View Slide

13. A Little About Me
    Next 23 years big companies and small
    

    View Slide

14. Things I Care About
    • Low cost of ownership
    • Bulletproof code
    • Maintainable Code (Clean Code and SOLID principle)
    • Leverage code generation
    • Reduce code needed to accomplish the goal
    x
    

    View Slide

15. Recap of
    Relational
    Database Features
    

    View Slide

16. Purpose of a Database?
    

    View Slide

17. Purpose of a Database?
    To allow data to be saved and retrieved in a reliable and performant manner.
    

    View Slide

18. What is a Relational Database?
    

    View Slide

19. What is a Relational Database?
    A collection of data items organized as a set of formally-described tables from which data
    can be accessed or reassembled in many different ways without having to reorganize the
    database tables.
    Application agnostic design
    

    View Slide

20. History
    Edgar F. Codd
    1970
    https://en.wikipedia.org/wiki/Edgar_F._Codd
    Hard drives were big
    and expensive
    In the 1970’s
    

    View Slide

21. Features of a Relational Database
    

    View Slide

22. Tables
    Constraints
    Data types
    Keys
    

    View Slide

23. Relationships
    Primary Keys
    Foreign Key
    

    View Slide

24. Normal Forms
    1nf – one value per field
    2nf – All the non-key columns in a table depend directly or indirectly on the primary key.
    3nf – columns depend directly key columns only
    • https://en.wikipedia.org/wiki/ACID
    

    View Slide

25. Views
    

    View Slide

26. Stored Procedures
    

    View Slide

27. “ACID” Transactions
    Atomic – all or nothing
    Consistent – completed transaction result in a valid state
    Isolated – transactions do not interfere with each other except to roll them back
    Durability – once a transaction completes, it will remain in effect even after a power loss.
    • https://en.wikipedia.org/wiki/ACID
    

    View Slide

28. Security
    Authorization
    • Server level
    • Database level
    • Schema level
    • Object level
    Authentication
    • Windows
    • SQL Server
    • Mixed
    Roles
    • Server
    • Database
    • Application
    

    View Slide

29. Logical and Physical Architecture
    SQL Server
    Clients
    Normal Usage Scenario
    

    View Slide

30. Logical and Physical Architecture
    SQL Server
    SQL Server
    Failover Cluster
    Clients
    

    View Slide

31. Logical and Physical Architecture
    SQL Server SQL Server
    Clients
    Replication Cluster
    Write actions Read actions
    Clients
    Publish
    x
    

    View Slide

32. Anatomy of a
    Hospital Claim
    UB-04
    

    View Slide

33. UB-04 Claim
    Form
    

    View Slide

34. UB-04
    Provider Info
    

    View Slide

35. UB-04
    Claim Info
    

    View Slide

36. UB-04
    Patient Info
    

    View Slide

37. UB-04
    Visit Info
    

    View Slide

38. UB-04
    Service Lines
    

    View Slide

39. UB-04
    Payer Info
    

    View Slide

40. UB-04
    Diagnosis
    Codes
    

    View Slide

41. UB-04
    Procedure
    Codes
    

    View Slide

42. UB-04
    Physicians
    x
    

    View Slide

43. Two Possible
    Claims Models
    

    View Slide

44. Model #1
    

    View Slide

45. Model #2
    

    View Slide

46. Simple query
    Two table query
    Sub queries –
    Get claims with Attending physician “Rick Martins”
    Duplicate problem - have to decide what to do with duplicates
    Getting all data – lots of queries
    Example Queries
    

    View Slide

47. Get claim with ID 12 – 2 ms per head record
    Save a claim – have to handle each child explicitly
    Example Usage in C#
    x
    

    View Slide

48. Document
    Databases
    

    View Slide

49. What is a Document Database?
    

    View Slide

50. What is a Document Database?
    A system for storing semi structured data, usually in the form of JSON.
    Advantages include being able to design data models that support common data access
    patterns and being able to access relevant data with a single query.
    Specifically, MongoDB does not support joins and multi table transactions. This allows
    scaling out on commodity hardware by design. Developers can design their applications
    without being concerned about deployment details.
    * https://university.mongodb.com/
    

    View Slide

51. What is a Document Database?
    Scalability & Performance
    Functionality
    Key/value stores
    RDBMS
    MongoDB
    * https://university.mongodb.com/
    

    View Slide

52. Documents
    

    View Slide

53. Collections
    Invoices Claims
    Users
    

    View Slide

54. JavaScript Shell Queries - Insert
    Insert One document
    Insert Many documents
    

    View Slide

55. JavaScript Shell Queries - Find
    Find Many documents
    Find Many with filter documents
    

    View Slide

56. Aggregation Framework
    Mongo’s answer to SQL’s Group By Clause and Aggregation functions.
    $project $match $group $sort
    Collection
    Result
    Aggregation Pipeline
    $skip $limit
    $unwind $out
    

    View Slide

57. Aggregation Framework - Example
    https://docs.mongodb.com/manual/tutorial/aggregation-zip-code-data-set/
    

    View Slide

58. JavaScript Shell Queries - Update
    Update Many documents
    

    View Slide

59. JavaScript Shell Queries – Remove, Drop
    Remove Many documents
    Drop Collection
    

    View Slide

60. Logical Architecture - Replica Sets
    Node 3
    Node 1
    Node 2
    

    View Slide

61. Logical Architecture - Shards
    Sharded Cluster A-M Sharded Cluster N-Z
    Mongo Routing
    Service (mongos)
    Node 3
    Node 2
    Primary
    Node
    Node 3
    Node 2
    Primary
    Node
    

    View Slide

62. Security
    Authorization
    • Server level
    • Database level
    • Collection level
    Authentication
    • Trusted environment
    • MongoDB Authentication
    Roles
    • Server
    • Database
    • Collection
    • User Defined
    subnet
    Web
    Server
    Mongo
    

    View Slide

63. MongoDB “Hack” Explained
    x
    

    View Slide

64. A Claim
    Document
    

    View Slide

65. db.claims.findOne({})
    Let’s look at a Claim Document
    

    View Slide

66. Simple query
    Two table query
    Sub queries –
    Get claims with Attending physician “Rick Martins”
    Duplicate problem - have to decide what to do with duplicates
    Getting all data – lots of queries
    Example Queries
    

    View Slide

67. Get claim with ID 12 – 2 ms per head record
    Save a claim – have to handle each child explicitly
    Example Usage in C#
    x
    

    View Slide

68. Insights and
    Opinions
    

    View Slide

69. • Easy to refactor
    • Performant
    • Horizontal scalability by design
    • Reduced complexity
    • No DBA required (sort of)
    • No need for Object Relationship Mappers (ORM)
    • Data is more readable
    • Less code
    • Free, sort of
    Benefits of MongoDB over SQL Server
    

    View Slide

70. • Supports Transactions
    • Structure
    • Data Validation
    • Familiar
    Benefits of SQL Server over MongoDB
    

    View Slide

71. • Learning takes time - start small to gain experience
    Lessons Learned using MongoDB
    

    View Slide

72. • Learning takes time - start small to gain experience
    • Might still need someone to focus on infrastructure
    Lessons Learned using MongoDB
    

    View Slide

73. • Learning takes time - start small to gain experience
    • Might still need someone to focus on infrastructure
    • Need to think through data validation
    Lessons Learned using MongoDB
    

    View Slide

74. • Learning takes time - start small to gain experience
    • Might still need someone to focus on infrastructure
    • Need to think through data validation
    • MongoDB can be used in Lieu of most RDBMS data stores
    Lessons Learned using MongoDB
    

    View Slide

75. • Learning takes time - start small to gain experience
    • Might still need someone to focus on infrastructure
    • Need to think through data validation
    • MongoDB can be used in Lieu of most RDBMS data stores
    • Replacing is expensive and risky.
    Lessons Learned using MongoDB
    

    View Slide

76. • Learning takes time - start small to gain experience
    • Might still need someone to focus on infrastructure
    • Need to think through data validation
    • MongoDB can be used in Lieu of most RDBMS data stores
    • Replacing is expensive and risky.
    • Semi-technical people may need to learn a new way of querying
    Lessons Learned using MongoDB
    

    View Slide

77. When would I prefer a RDBMS over a
    document database?
    

    View Slide

78. When would I prefer a RDBMS over a
    document database?
    • Brown-field apps that are fast enough
    

    View Slide

79. When would I prefer a RDBMS over a
    document database?
    • Brown-field apps that are fast enough
    • Financial apps that require ACID transactions
    

    View Slide

80. When would I prefer a RDBMS over a
    document database?
    • Brown-field apps that are fast enough
    • Financial apps that require ACID transactions
    • Ad Hoc Reporting databases
    

    View Slide

81. When would I prefer a document database
    over a RDBMS?
    

    View Slide

82. When would I prefer a document database
    over a RDBMS?
    • Brown-field apps that need to scale better
    

    View Slide

83. When would I prefer a document database
    over a RDBMS?
    • Brown-field apps that need to scale better
    • All green-field apps
    x
    

    View Slide

84. CosmosDB
    

    View Slide

85. Compelling Features
    1. Supports SQL queries
    2. Supports MongoDB’s API
    3. Eliminates deployment concerns
    

    View Slide

86. How to Evolve from RDBMS to NoSQL + SQL
    https://mapr.com/blog/how-evolve-rdbms-nosql-sql/
    Mongo University
    https://university.mongodb.com/
    SQL to Mongo Comparison
    https://docs.mongodb.com/manual/reference/sql-comparison/
    BSON Specification
    http://bsonspec.org/
    Aggregation Pipeline
    https://docs.mongodb.com/manual/aggregation/
    https://docs.mongodb.com/manual/tutorial/aggregation-zip-code-data-set/
    Azure Cosmos DB
    https://azure.microsoft.com/en-us/services/cosmos-db/?v=17.45b
    “UB-04 Handbook For Hospital Billing” (book) – Author Claudia Birkenshaw, M.S.A
    Claim Demo App
    https://github.com/dougcorbett/ClaimsDemo
    Reference Materials
    

    View Slide