After reading this article, I felt amazed...how this people maintaining reddit with two tables ?. It looks like, Initially they didn't concentrate for perfect relational DB. They went with simple schema for adding new features, and not to stuck by DB rules. Here I wrote good relational DB structure for Reddit like websites. Find below for explanation of tables
 

Reddit_Post

This table is for maintaining user posts and sub reddit information

Create Statement

CREATE TABLE reddit_post
(
  parent_id NUMBER(50) NOT NULL,
  post_id NUMBER(50) AUTO_INCREMENT,
  title varchar(200) NOT NULL,
  content varchar(2000) NOT NULL,
  link varchar(2083) NOT NULL,
  user_id varchar(25) NOT NULL,
  pic varchar(2083),
  status varchar(15) NOT NULL,
  type varchar(15) NOT NULL,
  votes INTEGER,
  created_time TIMESTAMP default CURRENT_TIMESTAMP,
  primary key(post_id)
);

Reddit_Votes

This table is for maintaining votes for posts

Create statement

CREATE TABLE reddit_votes
(
  post_id NUMBER(50) NOT NULL,
  user_id VARCHAR(25) NOT NULL,
  vflag SMALLINT NOT NULL,
  last_updated_time TIMESTAMP default CURRENT_TIMESTAMP,
  primary key(post_id,user_id),
  foreign key(post_id) references reddit_post(post_id),
);

Reddit_User

This table is for maintaining User Information. Here only 3 main columns are there. User name will be saved as "1234", "name", "srinivas". In this way, whole user information will be saved

Create statement 

CREATE TABLE reddit_user
(
  user_id VARCHAR(25),
  property VARCHAR(25) NOT NULL,
  value varchar(200) NOT NULL,
  last_updated_time TIMESTAMP default CURRENT_TIMESTAMP,
  primary key(user_id,property,value)
);

Reddit_Ranks

This table is for storing ranks for posts. Based these ranks, display order for posts will be calculated 

Create Statement

CREATE TABLE reddit_ranks
(
  post_id NUMBER(50) NOT NULL,
  hot double,
  new double,
  raising double,
  controversial double,
  top double,
  foreign key(post_id) references reddit_post(post_id)
);

Reddit_rights

This table maintains rights for users over subreddits and posts. To control spam, Sub reddits can be maintained by administrators. These people needs special rights to control Spam. Those type of rights can be maintained here
CREATE TABLE reddit_rights
(
  post_id NUMBER(50) NOT NULL,
  property VARCHAR(25),
  user_id VARCHAR(25),
  assigned_user_id VARCHAR(25),
  foreign key(post_id) references reddit_post(post_id),
  foreign key(user_id) references reddit_user(user_id),
  foreign key(assigned_user_id) references reddit_user(user_id)
);

Queries

###Insert statements for users

INSERT INTO reddit_user(user_id, property, value) values('admin','name', 'admin');
INSERT INTO reddit_user(user_id, property, value) values('srinivas','name', 'srinivas dasari');
INSERT INTO reddit_user(user_id, property, value) values('ramesh','name', 'ramesh');

###Insert statements for sub reddit

INSERT INTO reddit_post(parent_id, title, content, link, user_id, pic, status, type, votes) VALUES(0,'java','sub reddit for java developers','/r/java','admin','java_subreddit.png', 'active', 'subreddit', 0);
INSERT INTO reddit_post(parent_id, title, content, link, user_id, pic, status, type, votes) VALUES(0,'angularjs','sub reddit for AngularJS developers','/r/angularjs','admin','angularjs_subreddit.png', 'active', 'subreddit', 0);
INSERT INTO reddit_post(parent_id, title, content, link, user_id, pic, status, type, votes) VALUES(0,'sql','sub reddit for sql developers','/r/sql','admin','sql_subreddit.png', 'active', 'subreddit', 0);

###Insert statements for posts

INSERT INTO reddit_post(parent_id, title, content, link, user_id, pic, status, type, votes) VALUES(1,'Java Restful Webservice Tutorial with Sample case study','','http://blog.sodhanalibrary.com/2013/09/restful-web-service-tutorial-with-java.html','srinivas','http://4.bp.blogspot.com/-IWnld2WXOCg/UinxJK9ahMI/AAAAAAAAAAw/b8mFC6bl41w/s1600/screen2.bmp', 'active', 'post', 1);
INSERT INTO reddit_votes(post_id, user_id, vflag) VALUES(LAST_INSERT_ID(),'srinivas',1);

INSERT INTO reddit_post(parent_id, title, content, link, user_id, pic, status, type, votes) VALUES(2,'Responsive Web Design Using AngularJS Material UI','','http://blog.sodhanalibrary.com/2015/08/responsive-web-design-using-angularjs.html','srinivas','',  'active', 'post', 1);
INSERT INTO reddit_votes(post_id, user_id, vflag) VALUES(LAST_INSERT_ID(),'srinivas',1);

INSERT INTO reddit_post(parent_id, title, content, link, user_id, pic, status, type, votes) VALUES(2,'Responsive Web Design Using AngularJS Material UI','','http://blog.sodhanalibrary.com/2015/08/responsive-web-design-using-angularjs.html','srinivas','',  'active', 'post', 1);
INSERT INTO reddit_votes(post_id, user_id, vflag) VALUES(LAST_INSERT_ID(),'srinivas',1);

###Insert statements for comments

INSERT INTO reddit_post(parent_id, title, content, link, user_id, pic, status, type, votes) VALUES(4,'','good one','/r/java/4/comments','ramesh','','active', 'comment', 1);
INSERT INTO reddit_votes(post_id, user_id, vflag) VALUES(LAST_INSERT_ID(),'srinivas',1);

###select all sub reddits 

select * from reddit_post where parent_id = 0; 

###select all posts from sub reddit java

select * from reddit_post where parent_id = 1 and type='post';
select * from reddit_post where link = '/r/java'  and  type='post';
select * from reddit_post where title = 'java' and type='post';

###select comments for post

select * from reddit_post where post_id = 7 and type='comment';

4 comments:

  1. New programmer are now getting more information about the reddit database. The work on rush essay data is very difficult for all the new users because its difficult to understand. Here is very different way which helps us to understand that.

    ReplyDelete
  2. I was very impressed by this post, this site has always been pleasant news Thank you very much for such an interesting post, and I meet them more often then I visited this site. EDDM® Every Door Direct Mail® printing

    ReplyDelete
  3. I am happy to find this post very useful for me, as it contains lot of information. I always prefer to read the quality content and this thing I found in you post. Thanks for sharing. buy upvotes reddit,

    ReplyDelete

Blogroll

Follow this blog by Email

Popular Posts