What would your DB Schema look like if you are building a Reddit/Digg-like site?

Question

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.

Source: http://thinkvitamin.com/code/steve-huffman-on-lessons-learned-at-reddit/

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?

Answer 1

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

Answer 2

The Reddit software is open source - download it and see.

You will need to have a table dedicated to kittens, though.