When should you use a document vs relational vs graph database? [closed]

Question

For the purposes of discussion let's consider a FourSquare scenario.

Scenario

Entities:

• Users
• Places

Relationships:

• Checkins: users <-> places, many to many
• Friends: users <-> users, many to many

Database Design

These will most likely have errors, please point them out.

RDBMS

Tables:

• Users
• Places
• Checkins (junction)
• Friends (junction)

Pros:

• CAP: consistency, availability

Cons:

• CAP: partition tolerance, aka sharding
• schemes = inflexible structure
• poor replication?

Graph

Objects:

• Users
• Places

Edges:

• Friends: User <-> User
• Checkins: User -> Places
  • contains timestamp

Pros:

• CAP: consistency, availability?
• schemaless, easily mutable objects and edges
• graph traversal queries, for example:
  • clustering
    • finding groups of friends
    • finding restaurants liked by similar people
  • any other common / useful queries?

Cons:

• CAP: partition tolerance?

Document / Object

3 separate databases?

• Users
  • friends list
• Checkins
  • timestamp
  • user
  • place
• Places

Pros:

• CAP: availability, partition tolerance
• schemaless, easily mutable objects

Cons:

• CAP: consistency

Questions

For the record, they ended up using MongoDB. In addition to all those question marks above:

1. I'm not sure how to implement a document database.
2. How do document databases gain partition tolerance?
3. To get a single user's checkins, I assume the operation would parse all checkins and filter the metadata for username (map + filter). The performance of parsing 1,000,000+ documents for each user would be terribly poor. I assume this is not the correct behavior?
4. What other pro / cons are there?

Answer 1

Your question could be the topic of a semester-long college course. You need to break it down into manageable chunks. As such, I will just throw out some partial answers.

One of the first things to look at in deciding which kind of database to use is what kind of queries you will run and whether you will know them all in advance of creating the database. SQL databases have the advantage of powerful and flexible queries across all the data in the database. Graph databases have highly specialized query capabilities that make them the best for graph data and really bad for non-graph data (though graph databases can be components in SQL databases). NoSQL databases are much more limited in their ability to retrieve and operate on data.

The next is how you feel about the ACID properties: Atomicity, Consistency, Isolation, and Durability. SQL databases provide strong guarantees about all 4. NoSQL databases typically do not promise all 4, and the ways they depart are among the key differences that differentiate the various NoSQL database implementations. On the other hand, it is not possible to guarantee Consistency and Availability in the face of a Partition (see Brewer's CAP thorem), so no SQL database will do if you insist on full Availability in the face of a Partition. Personally, I care a lot about Durability of the data in the database, as I typically work with data where even a 0.0001% data loss is unacceptable, and the data sets are small enough that I do not need to worry about partitions, so I heavily favor SQL databases.

Another very practical consideration is the quality of the server code, the availability of database administrators and programmers, the quality of the support available for problems that arise, the quality and availability of interface libraries to connect your application to the database, and so on. MySQL has been around for almost 2 decades, has the vast majority of bugs worked out, is very widely used and so has both great support and great availability of personnel, and is likely to be supported for the next 10 years. You cannot say any of those things about Riak.

Note that while Google practically invented NoSQL databases so they could store a cached and indexed version of the entire world wide web, they still use MySQL for some things.