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';

0 comments:

Blogroll

Follow this blog by Email

Popular Posts