Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?
share|improve this question

closed as not constructive by maple_shaft May 11 '12 at 16:44

As it currently stands, this question is not a good fit for our Q&A format. We expect answers to be supported by facts, references, or expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, visit the help center for guidance.If this question can be reworded to fit the rules in the help center, please edit the question.

    
(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'. –  Emmad Kareem May 10 '12 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 '12 at 23:52
add comment

1 Answer

up vote 3 down vote accepted

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.

share|improve this answer
1  
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 '12 at 2:43
    
@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 '12 at 15:32
add comment

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