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



  • Users
  • Places


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

Database Design

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



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


  • CAP: consistency, availability


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



  • Users
  • Places


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


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


  • CAP: partition tolerance?

Document / Object

3 separate databases?

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


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


  • CAP: consistency


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?
  • (1) You need to spell out the realtionship between 2 tables in business term. This is because there may be parallel relationships. For example, users <--> users does not imply 1 m-m relationship. It could mean more than 1. For example: A user likes another user and a user hates another users. These are 2 relationships. (2) It would help if you could summarize what you want 'exactly'.
    – NoChance
    May 10, 2012 at 22:13
  • @EmmadKareem: (1) I'm not looking to complicate the scenario. The only user <-> user relationship I'm interested in is a mutual friendship, which is a many to many connection. (2) I'd like the 4 questions listed at the bottom of the post answered.
    – wting
    May 10, 2012 at 23:52

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

  • 2
    I realize I was asking a lot, so a general answer would have been fine. The core questions are: (1) Why use document database for supposed great sharding when you can implement horizontally sharding in logic using range sharding? (2) How would you design a document database to use in a FourSquare scenario and how does it handle some common uses (show user's checkins, show user's friends, show place's users currently checked in)?
    – wting
    May 11, 2012 at 2:43
  • 2
    @William, there are dozens of articles answering your questions easily accessible via Google. Even several on Stack Overflow alone. Do your homework.
    – Old Pro
    May 11, 2012 at 15:32

Not the answer you're looking for? Browse other questions tagged or ask your own question.