i'm bulding website instructors upload courses , publish them. i'm planning database , have few questions in mind. consider following tables:
instructors(id(pk), fullname, email, password, created, updated) categories(id(pk), title, description, created, updated) courses(id(pk), cat_id(fk), instructor_id(fk), title, description, created, updated) lessons(id(pk), course_id(fk), title, description, duration, created, updated)
i have made basic relationships between said tables. questions are:
- i want check
category
of particularlesson
. - i want check
lessons
belong particularcategory
.
would fine if put category_id
foreign key in lessons
table? way able lessons
in category joining tables. reverse relationship, can select category
by selecting course
.
please me out. in advance.
if lesson has 0 or 1 "categories", can put category_id
in lessons
. appropriate , correct.
if lesson have multiple categories, need junction table:
create table lesson_categories ( lesson_id . . ., category_id . . ., constraint fk_lesson_categories_lesson foreign key (lesson_id) references lessons(id), constraint fk_lesson_categories_category foreign key (category_id) references categories(id) );
Comments
Post a Comment