Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Just found out something rather interesting about Reddit's database schema:

The way we’ve changed is we use an “open schema”. Sometimes it’s called “entity attribute value”. It’s basically a large key value store. We have two types of tables for every data type. There is a “thing” table, and then a “data” table. Everything in Reddit is comprised of what we call things: users, links, comments, sub-Reddit’s, awards.

Everything on Reddit is a thing. The schema for those elements look the same. It looks like this top table here: ups, downs, a type, a creation date, some properties that are fundamental across all of the objects in Reddit.

Then we have what’s called the “data” table, which is basically this huge table with three columns: the thing idea we’re talking about is the left-most column, then a key, and a value. For example, these two links would be represented by two links in a thing table, and then one row in the data table for every value on that link. There would be a key for title, and a value for that title for that link; and a key for URL and a key for the author, and then a key for how many spam votes that are on it.


So I started looking around to see what other similar sites are doing. Could not find anything about Digg. SO is doing something more traditional with a Users, Posts, Comments, Badges and Votes table. DotNetKicks and DotNetShout do similar (tradional) things.

So I wanted to ask the SO community, if you were designing a site like Reddit or Digg or even SO (basically any social voting sites) using ASP.NET MVC and SQL Server, what would you do? How would you design it?

share|improve this question

closed as too broad by gnat, GlenH7, Corbin March, MichaelT, Dan Pichelman Aug 29 '13 at 21:16

There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs.If this question can be reworded to fit the rules in the help center, please edit the question.

That design sounds more like NoSQL than a standard RDMS. A single monstrous table with only key-value pairs defeats the whole point of RDMS

As I only have experience with RDMS, I can't speak from a NoSQL point of view. From what little I was able to gleam from the sites (I don't visit them), it seems like a pretty standard user generated site.

Essentially you have nodes (in Drupal speak) or articles that individual users post, so thats in a table. You then have comments to each article, so you have another table for comments linked to the post and to the parent comment (if your comment system uses nested replys). You have users, which is another table. You then have a basic Votes table with the users ID and a +1 or -1.

That would really be the gist of it. Anything more is very implementation specific

share|improve this answer
Yep, that quote got me wondering what database they were using, and later on in the article he confirms Postgres. He even says it's "designed to store data relationally and we’re not using it in that way, so we don’t get to take advantage of all the cool relational things". It does make you wonder why they didn't also switch to a NoSQL store. – Carson63000 Apr 2 '11 at 6:28