'How to store nested data in MySQL Database? [duplicate]

So I'm making a course table which stores course details, which will be displayed on my website. Now each course will have multiple videos/chapters. Eg: A course named stone age, might have 3 chapters pre historic, mediaeval, modern. I need to store link to these three videos under a single course which is "stone age". How do I do that?

  • Stone Age
    • pre historic
    • mediaeval
    • modern
  • course 2 etc...

My course table has coursename, coursesubject, coursedescription, cprice, cid, cimg. Do I need to make another table to store the media content for this course? Or can it be done using a single table?

mysql


Solution 1:[1]

I suggest that you create a column for the course, one for the chapter and finally one for the video.

CREATE TABLE course_videos (
   CourseName VARCHAR(25),
   Chapter VARCHAR(25),
   Video VARCHAR(50)
   );

I'm assuming that you're storing a URL for the video and not the video itself. An entry might be:

INSERT INTO course_videos VALUES (
  'Stone Age' ,
   'pre historique'
   'name of the video.mp4'
   );

One simple table will handle the 2 levels of the heirarchy. For the given application I don't anticipate a large number of entries ( <1000) so storage space and optimization of performance are not concerns.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions