title: MySQL database design of an online video education website
body: <p>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. </p>
  
  <p>There are </p>
  
  <p><strong>USER PROFILE</strong></p>
  
  <p>username    VARCHAR</p>
  
  <p>id          INT </p>
  
  <p>Type            ENUM    </p>
  
  <p>Email           VARCHAR </p>
  
  <p>Date_created    TIMESTAMP</p>
  
  <p>Date_modified   TIMESTAMP</p>
  
  <p>Pass            VARBINARY</p>
  
  <p>Name            VARCHAR</p>
  
  <p>certificates    VARBINARY</p>
  
  <p>Ranking_points  INT </p>
  
  <p>Gender          ENUM    </p>
  
  <p>DOB         DATE    </p>
  
  <p>Avatar_url  VARCHAR</p>
  
  <hr>
  
  <p><strong>VIDEOS</strong></p>
  
  <p>Title           VARCHAR</p>
  
  <p>id          INT </p>
  
  <p>Category    ENUM    </p>
  
  <p>Description TINYTEXT</p>
  
  <p>Date_created    TIMESTAMP</p>
  
  <p>Tmp_name    CHAR    </p>
  
  <p>File_name   VARCHAR </p>
  
  <p>Size            MEDIUMINT</p>
  
  <p>Subtitle_url    VARCHAR </p>
  
  <p>Liked_count INT </p>
  
  <p>Shared_count    INT </p>
  
  <p>Tags            ENUM    </p>
  
  <hr>
  
  <p><strong>CATEGORY</strong></p>
  
  <p>Category    VARCHAR </p>
  
  <p>id          INT </p>
  
  <p>Description TINYTEXT    </p>
  
  <hr>
  
  <p><strong>RecentlyViewed</strong></p>
  
  <p>User_id         INT </p>
  
  <p>Video_id    INT </p>
  
  <p>Viewed_date TIMESTAMP   </p>
  
  <hr>
  
  <p><strong>FRIEND_LIST</strong></p>
  
  <p>User_id         INT </p>
  
  <p>Friend_id   INT</p>
  
  <hr>
  
  <p><strong>USER_QUESTIONS</strong></p>
  
  <p>User_id       INT   </p>
  
  <p>Question_id   INT   </p>
  
  <p>Question_title    VARCHAR</p>
  
  <p>Question_content  LONGTEXT</p>
  
  <p>Date_asked    TIMESTAMP </p>
  
  <hr>
  
  <p><strong>VIDEO_COMMENTS</strong>
  Title             VARCHAR</p>
  
  <p>Video_id      INT   </p>
  
  <p>Comment_content   LONGTEXT  </p>
  
  <p>User_id       TINYTEXT  </p>
  
  <p>Date_created      TIMESTAMP </p>
  
  <hr>
  
  <p><strong>USER_MESSAGES</strong></p>
  
  <p>User_id         INT </p>
  
  <p>Message_id  INT </p>
  
  <p>Message_content LONGTEXT    </p>
  
  <p>Sender_id   INT </p>
  
  <p>Date_created    TIMESTAMP</p>
  
  <hr>
  
  <p><strong>ONLINE EXAMS/ASSESSMENTS</strong></p>
  
  <p>id          VARCHAR</p>
  
  <p>type            ENUM    </p>
  
  <p>exam_url    VARCHAR</p>
  
  <p>description TINYTEXT</p>
  
  <p>Date_created    TIMESTAMP</p>
  
  <hr>
  
  <p><strong>EXAMS_TAKEN_BY_USER</strong></p>
  
  <p>exam_id         INT </p>
  
  <p>User_id         INT </p>
  
  <p>Exam_result SMALLINT    </p>
  
  <p>Date_taken  TIMESTAMP   </p>
  
  <p>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?</p>