Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Relational vs. Document DBs - How do I decide?

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.

E710a7268891ac8c9c13756fcd28fa5f?s=128

Doug Corbett

November 28, 2017
Tweet

Transcript

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

    28. 2017
  2. Agenda A Little About Me

  3. Agenda A Little About Me Recap of Relational Database Features

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

    Anatomy of a Claim Document Database Features Claims Document CosmosDB Possible Claim Models
  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
  6. Doug Corbett Founder and Principal Consultant Lionheart Consultants dougccorbett@gmail.com

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

  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
  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
  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/
  11. A Little About Me – Dose of Reality • https://en.wikipedia.org/wiki/Pong

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

    • https://www.rottentomatoes.com/m/shrek_forever_after/
  13. A Little About Me Next 23 years big companies and

    small
  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
  15. Recap of Relational Database Features

  16. Purpose of a Database?

  17. Purpose of a Database? To allow data to be saved

    and retrieved in a reliable and performant manner.
  18. What is a Relational Database?

  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
  20. History Edgar F. Codd 1970 https://en.wikipedia.org/wiki/Edgar_F._Codd Hard drives were big

    and expensive In the 1970’s
  21. Features of a Relational Database

  22. Tables Constraints Data types Keys

  23. Relationships Primary Keys Foreign Key

  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
  25. Views

  26. Stored Procedures

  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
  28. Security Authorization • Server level • Database level • Schema

    level • Object level Authentication • Windows • SQL Server • Mixed Roles • Server • Database • Application
  29. Logical and Physical Architecture SQL Server Clients Normal Usage Scenario

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

    Clients
  31. Logical and Physical Architecture SQL Server SQL Server Clients Replication

    Cluster Write actions Read actions Clients Publish x
  32. Anatomy of a Hospital Claim UB-04

  33. UB-04 Claim Form

  34. UB-04 Provider Info

  35. UB-04 Claim Info

  36. UB-04 Patient Info

  37. UB-04 Visit Info

  38. UB-04 Service Lines

  39. UB-04 Payer Info

  40. UB-04 Diagnosis Codes

  41. UB-04 Procedure Codes

  42. UB-04 Physicians x

  43. Two Possible Claims Models

  44. Model #1

  45. Model #2

  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
  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
  48. Document Databases

  49. What is a Document Database?

  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/
  51. What is a Document Database? Scalability & Performance Functionality Key/value

    stores RDBMS MongoDB * https://university.mongodb.com/
  52. Documents

  53. Collections Invoices Claims Users

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

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

    with filter documents
  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
  57. Aggregation Framework - Example https://docs.mongodb.com/manual/tutorial/aggregation-zip-code-data-set/

  58. JavaScript Shell Queries - Update Update Many documents

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

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

    2
  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
  62. Security Authorization • Server level • Database level • Collection

    level Authentication • Trusted environment • MongoDB Authentication Roles • Server • Database • Collection • User Defined subnet Web Server Mongo
  63. MongoDB “Hack” Explained x

  64. A Claim Document

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

  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
  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
  68. Insights and Opinions

  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
  70. • Supports Transactions • Structure • Data Validation • Familiar

    Benefits of SQL Server over MongoDB
  71. • Learning takes time - start small to gain experience

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

    • Might still need someone to focus on infrastructure Lessons Learned using MongoDB
  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
  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
  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
  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
  77. When would I prefer a RDBMS over a document database?

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

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

    • Brown-field apps that are fast enough • Financial apps that require ACID transactions
  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
  81. When would I prefer a document database over a RDBMS?

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

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

    • Brown-field apps that need to scale better • All green-field apps x
  84. CosmosDB

  85. Compelling Features 1. Supports SQL queries 2. Supports MongoDB’s API

    3. Eliminates deployment concerns
  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