关于数据库表的设计

Question

如图所示

我的项目需要将已经爬取的coursera数据导入数据库，以备后续工作使用，如php等
现在数据已经爬取完毕，如图格式存在文件夹中，图片所示属于一个目录，我是一个数据菜鸟，不知道如何设计数据库才能将这几级数据正确的存在mysql中呢？（使用navicat），望各位高手不吝赐教，小弟感激不尽！

Answer 1

很简单，你需要把课程和目录分开保存，我举个例子：

create table course(
    id int not null auto_increment,
    course_name varchar(32),
    url varchar(64),
    category_id int,
    course_desc varchar(512),
    primary key (id),
    foreign key (category_id) references category(id) on delete cascade
);

create table category(
    id int not null auto_increment,
    category_name varchar(32),
    parent_id int,
    primary key (id),
    foreign key (parent_id) references category(id) on delete cascade
);
    
insert into category(id, category_name, parent_id)
values (1, "Computer science", null), (2, "Algorithms", 1), (3, "Design & Product", 1), (4, "Software development", 1);

insert into course(id, course_name, url, category_id, course_desc)
values (1, "Software Engineering Management", "https://course.scut.cn/sem", 4,
"Software engineering management could be described simply as a management position in the software industry. ");    

外键是让你看清楚表的关系，并不是一定要有。

mysql> select * from course \G
*************************** 1. row ***************************
         id: 1
course_name: Software Engineering Management
        url: https://course.scut.cn/sem
category_id: 4
course_desc: Software engineering management could be described simply as a management position in the software industry. 
1 row in set (0.00 sec)

mysql> select * from category;
+----+----------------------+-----------+
| id | category_name        | parent_id |
+----+----------------------+-----------+
|  1 | Computer science     |      NULL |
|  2 | Algorithms           |         1 |
|  3 | Design & Product     |         1 |
|  4 | Software development |         1 |
+----+----------------------+-----------+
4 rows in set (0.00 sec)

Answer 2

id 课程编号
pid 课程父节点编号
name 课名
desc 课信息
url 课程url
...