Database design and join operations among database tables

Question

The theme of this question is to maintain the user comments over my website.

I had around 25000 articles on my website(of different categories) and each article has a comments section below it.Since the number of comments increased over 70,000 I decided to divide the articles into various tables depending on its category articles_of_type_category and a corresponding comments table article_category_comments for each table,assuming that it would improve the performance in future (though currently its working fine)

Now I have two questions :

1) Should I divide the database or there will no degradation in performance if table grows further in size?

2)If yes,then I have some problem in SQL for join operation for the new database design.On the comments page for each article I show the comments,name of the person who made the comment and his points.

So suppose user is viewing the article 3, hence I need to obtain the following detail to show on the page of article 3

-------------------------------------------------------------------------------------------
serial#|  comment  | name_who_made_this_comment |  points  |  gold  |  silver  | bronze
-------------------------------------------------------------------------------------------
       |           |                            |          |        |          | 
       |           |                            |          |        |          |

by joining these three tables

user_details

+----+--------+----------+
| id | name   | college  |
+----+--------+----------+
|  1 | naveen | a        |
|  2 | rahul  | b        |
|  3 | dave   | c        |
|  4 | tom    | d        |
+----+--------+----------+

score (this table stores the user points like stackoverflow)

+----+--------+------+--------+--------+---------+
| id | points | gold | silver | bronze | user_id |
+----+--------+------+--------+--------+---------+
|  1 |   2354 |    2 |      9 |     25 |    3    |
|  2 |   4562 |    1 |      9 |     11 |    2    |
|  3 |   1123 |    7 |      9 |     11 |    1    |
|  4 |   3457 |    0 |      9 |      4 |    4    |
+----+--------+------+--------+--------+---------+

comments (this table stores comment, id of the article on which it was made,and user id)

+----+----------------------------+-------------+---------+
| id | comment                    |  article_id | user_id |
+----+----------------------------+-------------+---------+
|  1 | This is a nice article     |           3 |       1 |
|  2 | This is a tough article    |           3 |       4 |
|  3 | This is a good article     |           2 |       7 |
|  4 | This is a good article     |           1 |       3 |
|  5 | Please update this article |           4 |       4 |
+----+----------------------------+-------------+---------+

I tried something like

select * from comments join (select * from user_details join points where user_details.id=points.user_id)as joined_temp where comments.id=joined_temp.u_id and article_id=3;

Answer 1

I would start with a table with articles and categories. Then use a bridge table to link the both. My advice would be to index the categories in the bridge table. This would speed up the access.

Example of table structure:

CREATE TABLE Article (
 id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
 title varchar(100) NOT NULL 
  );

INSERT INTO Article
    (title)
VALUES 
('kljlkjlkjalk'),
('aiouiwiuiuaoijukj');

CREATE TABLE Category (
  id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name varchar(100)
  );

INSERT INTO Category
    (name)
VALUES 
('kljlkjlkjalk'),
('aiouiwiuiuaoijukj');


CREATE TABLE Article_Category (
  id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  article_id int,
  category_id int
);

INSERT INTO Article_Category
    (article_id, category_id)
VALUES 
(1,1),
(1,2);

CREATE TABLE User_Details
    (`id` int, `name` varchar(6), `college` varchar(1))
;

INSERT INTO User_Details
    (`id`, `name`, `college`)
VALUES
    (1, 'naveen', 'a'),
    (2, 'rahul', 'b'),
    (3, 'dave', 'c'),
    (4, 'tom', 'd')
;

CREATE TABLE Score
    (`id` int, `points` int, `gold` int, `silver` int, `bronze` int, `user_id` int)
;

INSERT INTO Score
    (`id`, `points`, `gold`, `silver`, `bronze`, `user_id`)
VALUES
    (1, 2354, 2, 9, 25, 3),
    (2, 4562, 1, 9, 11, 2),
    (3, 1123, 7, 9, 11, 1),
    (4, 3457, 0, 9, 4, 4)
; 

CREATE TABLE Comment
    (`id` int, `comment` varchar(26), `article_id` int, `user_id` int)
;

INSERT INTO Comment
    (`id`, `comment`, `article_id`, `user_id`)
VALUES
    (1, 'This is a nice article', 3, 1),
    (2, 'This is a tough article', 3, 4),
    (3, 'This is a good article', 2, 7),
    (4, 'This is a good article', 1, 3),
    (5, 'Please update this article', 4, 4)
;

Try this:

SQLFiddle Demo

Best of luck.

Answer 2

70000 elements are not so many. In fact the number is close to nothing. Your problem lies in bad design. I have a table with many millions of records and when I request to the application server which executes complex queries in the backend and it responds in less than a second. So you are definitely doing something in sub-optimal design. I think that a detailed answer would take too much space and effort (as we have a complete science built on your question) which is out of scope in this website, so I choose to point you to the right direction:

1. Read about normalization (1NF, 2NF, 3NF, BCNF and so on) and compare it to your design.

2. Read about indexing and other implicit optimizations

3. Optimize your queries and minimize the number of queries

As to answer your concrete question: No, you should not "divide" your table. You should fix the structural errors in your database schema and optimize the algorithms using your database.

Answer 3

This is a response to this comment, "@DanBracuk:It would really be useful if you give an overview by naming the tables and corresponding column names"

 Table category
 categoryId  int not null, autoincrement  primary key
 category varchar(50)

Sample categories could be "Fairy Tale", "World War I", or "Movie Stars".

 Table article
 articleId int not null, autoincrement  primary key
 categoryId  int not null foreign key
 text clob, or whatever the mysql equivalent is

Since the comment was in response to my comment about articles and categories, this answer is limited to that.