sql - Confusion to form MySQL Primary & Foreign Key constraints -


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 particular lesson.
  • i want check lessons belong particular category.

would fine if put category_id foreign key in lessons table? way able lessons in category joining tables. reverse relationship, can select categoryby 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

Popular posts from this blog

resizing Telegram inline keyboard -

command line - How can a Python program background itself? -

php - "cURL error 28: Resolving timed out" on Wordpress on Azure App Service on Linux -