MySQL database design of an online video education website

Question

I am planning on a website that offers users training videos on multiple subjects, users can view videos, check their view history, comment on videos, taking relative exams and gain badges/certificates if they succeed in the exam. Also they can submit live questions when they had a problem during the video. ALso there will be a forum where users can help each other out and add friends, send messages, etc. WIth this idea in mind, I am now designing the database, but I had no experience in database design before, so please review the following design and give me some suggestions on performance and best practices. Thank you all in advance.

There are

USER PROFILE

username VARCHAR

id INT

Type ENUM

Email VARCHAR

Date_created TIMESTAMP

Date_modified TIMESTAMP

Pass VARBINARY

Name VARCHAR

certificates VARBINARY

Ranking_points INT

Gender ENUM

DOB DATE

Avatar_url VARCHAR

---

VIDEOS

Title VARCHAR

id INT

Category ENUM

Description TINYTEXT

Date_created TIMESTAMP

Tmp_name CHAR

File_name VARCHAR

Size MEDIUMINT

Subtitle_url VARCHAR

Liked_count INT

Shared_count INT

Tags ENUM

---

CATEGORY

Category VARCHAR

id INT

Description TINYTEXT

---

RecentlyViewed

User_id INT

Video_id INT

Viewed_date TIMESTAMP

---

FRIEND_LIST

User_id INT

Friend_id INT

---

USER_QUESTIONS

User_id INT

Question_id INT

Question_title VARCHAR

Question_content LONGTEXT

Date_asked TIMESTAMP

---

VIDEO_COMMENTS Title VARCHAR

Video_id INT

Comment_content LONGTEXT

User_id TINYTEXT

Date_created TIMESTAMP

---

USER_MESSAGES

User_id INT

Message_id INT

Message_content LONGTEXT

Sender_id INT

Date_created TIMESTAMP

---

ONLINE EXAMS/ASSESSMENTS

id VARCHAR

type ENUM

exam_url VARCHAR

description TINYTEXT

Date_created TIMESTAMP

---

EXAMS_TAKEN_BY_USER

exam_id INT

User_id INT

Exam_result SMALLINT

Date_taken TIMESTAMP

I have two more quesions: 1. I would like to allow comments on user comments, how would I design the database structure? 2. One video can belong to multiple categories, is it necessary to create a new table for video categories or just put a series of categories in the category field in the VIDEO table?

Answer 1

The overall structure seems to be okay, at least that looks pretty much like something I would do.

1.- About comments on comments:

I guess that the simplest design would be to add a parent_comment_id column to it. But that's a tricky one, because there would be a lot of NULL values either on parent_comment_id or video_id. So, another way around this would be to allow NULL values for video_id on VIDEO_COMMENT. In this scenario when a NULL in this column is found, you would know you're in presence of a reply to a user comment. Then, you'll have to found the parent comment on a second table:

USER_COMMENT
user_comment_id (primary key)
video_comment_id (foreign key to VIDEO_COMMENT)
comment (text)
created_date (date)

This is definitely not the cutest solution, but if you're troubled by performance issues, it could be worthy of a shot. You still have the same VIDEO_COMMENTS table (clean as before, no modifications to its structure). User comments on previous comment might not be that frequent, so you have them in a second structure, and not all over the same VIDEO_COMMENTS table.

2.- About Video Categories

Here I would create 2 additional tables: one for video categories (or tags) and a second for linking each video to its categories.

VIDEOS

Here I would drop the CATEGORY column


CATEGORIES

category_id (primary key)
title

VIDEO_CATEGORIES

video_category_id
category_id
video_id

In this case you can have a list of categories already stored, and the user would only have to select the ones he/she thinks right to his/her video.

I not recommend you to put multiple values on a same column since it's really a bad design issue. You couldn't provide the user with a list of categories (like the one I mentioned before). You would have, also, many duplicated values, typos, etc. Also, having several values stored in the same column makes editing unnecessary difficult: think how would you implement a user request to delete a category tag and add another. Of course, it isn't that hard, but it could be a lot simpler if you only had to delete individual references.

Answer 2

you can use self-linked table for user comments. The table would look like this

Id ---- comments ---- parent_comment_Id
1    root comments         null
2    child comments         1
3    grandchild comments    2 

About video category, it would be nice to have a separate table particularly for video category. And then you can scale category information such as category name, description and etc, in future. Considering user experience, it is quite common case that users want to check the category list first and then click through to get all the related videos.