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
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