Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases

Posted by Gabriel Martins
Gabriel Martins
30 Points

How to group topics into sections

I am building a web app for a school and I need to arrange the topics into sections.

I have a course model and I want to arrange the course content into sections, e.g. COURSE TITLE - LEARNING PHP

Getting Started with PHP What is php writing your first php syntax PHP and MYSQL making a connection to db listening for response this is what I came up with

CREATE TABLE courses ( course_id INT NOT NULL, title VARCHAR(50) NOT NULL, PRIMARY KEY(course_id) );

CREATE TABLE sections ( section_id INT NOT NULL, title VARCHAR(50) NOT NULL, course_id INT NOT NULL, PRIMARY KEY(section_id), FOREIGN KEY (course_id) REFERENCES COURSES(course_id) );

CREATE TABLE topics ( topic_id INT NOT NULL, section_id INT NOT NULL, title VARCHAR(50) NOT NULL, video_url varchar(255), PRIMARY KEY(section_id), FOREIGN KEY (section_id) REFERENCES SECTIONS(section_id) ); but my problem here is when i collect all sections related to a course, it is sorts the sections according to timestamp and I cant sort by title either because 1 can be zzzz and section 2 aaa.

please how do I go about this. if my schema is not appropriate, please guide me thanks

1 Answer

ivana kantnerova
ivana kantnerova
15,932 Points
ivana kantnerova
ivana kantnerova
15,932 Points

CREATE TABLE topics ( topic_id INT NOT NULL, section_id INT NOT NULL, title VARCHAR(50) NOT NULL, video_url varchar(255), PRIMARY KEY(section_id), FOREIGN KEY (section_id) REFERENCES SECTIONS(section_id) )... i supposed, here should be primary key(topic_id) and not section_id

in select, there a title should be with the table whose belongs ..
select sections.title, topics.title from courses inner join sections on courses.course_id = sections.course_id inner join topics on sections.section_id = topics.section_id where courses.title = '....';