CODEWITHSUNDEEP
databaseormnosqldocument-databaseodm
BenMorel
BenMorel

Reputation: 36632

Motivations for using relational database / ORM or document database / ODM

It's been a long time since I haven't created a project from scratch, and now document-oriented databases (as well as ODM) have become quite popular, so I have to consider them before blindly going the relational route.

Could anyone try to list the motivations / project criteria that could lead to one choice or another?

Upvotes: 2

Views: 2849

Answers (1)

Alex I
Alex I

Reputation: 20317

ORM / relational database / SQL

Pros:

  • well-understood, standard approach
  • maps well to data with consistent structure
  • maps well to data with multiple relationships between multiple entities
  • has extensive join capabilities
  • has transactions
  • scalable to huge number of transactions per second (using MySQL Cluster, Fusion-IO, etc)

Cons:

  • hard to scale to huge volume of data, if performance is also a concern
  • does not map well to data with variable structure (or semi-structured)
  • persisting objects requires a glue/translation layer which can be a performance bottleneck (and if done wrong also can be very verbose)

ODM / document database / NoSQL

Pros:

  • scalable to huge volume of data, and to huge numbers of relatively independent queries
  • high availability, sharding, multi-master, ...
  • maps well to semi-structured data
  • maps well to data with more variable structure
  • data model can be more flexible
  • queries do not have to be translated to SQL (the native NoSQL query style may or may not be better suited for some uses, and no overhead from SQL driver/parsing/etc)
  • (for object databases) maps directly to objects, no need for object-relational translation

Cons:

  • often, no join (or limited version of join)
  • often, no transactions (or a limited version of transactional consistency/atomicity)

How to decide

Based on the type of data and usage patterns:

  • Does the data have a uniform structure? (relational) ... or a variable/inconsistent structure? (document)
  • Does typical usage read/write a single type of entity? (document) ... or a view which is composed of properties of multiple entities? (relational)
  • Are transactions required? (relational) ... or no transactions needed? (document)

Based on the scaling/performance requirements:

  • Huge data + few, slow, complex read/writes? (data warehousing type scenario) => relational
  • Huge data + huge volume of simple read/writes? (craigslist backend type scenario) => document
  • Huge data + fast, complex reads/writes? => this is difficult; either use relational and try to scale it up, or use document and try to simplify queries
  • Moderate data + fast transactional writes? (banking type scenario) => relational
  • Moderate data + moderate read/write? => pick any based on vendor/tool support, familiarity, etc

References

(Background: I haven't done anything in this area very recently, but a few years ago I built a large system which used replicated MySQL + Sphinx, ie a relational and document hybrid)

Upvotes: 12

Related Questions