Database Design for an Online Course Website

[@DatabaseStar]

Bonus PDF: This ERD, descriptions of each table and column with sample data, and SQL scripts to create all of the tables on your own database: www.databasestar.com/dbdesign/?

[@roninspect4357]

i struggle a lot designing a database. These videos are really helpful.

[@DatabaseStar]

Glad you like them!

[@judaperez681]

I was exploring your youtube content... NEW SUB!

[@DatabaseStar]

Thanks! Glad you like it.

[@Alfenijonas]

Just leaving a comment so that more people might see this. Let's go DB fellows. :D

[@DatabaseStar]

Thanks!

[@felipemfrudeli]

you videos help me a lot. Im trying to start learning database design, and this channel is the best channel I can find, thx for videos, can you make a video about testing performance of database? (like put random data and stress)

[@DatabaseStar]

Good to hear! Sure, I can make a video on that. Is there anything specific you want to know or see with testing the performance of a database?

[@lovetheentertainment9949]

We need database design for cricket management along with their contracts, clubs, domestic structure etc

[@DatabaseStar]

Good idea! I have a "sports league" on my list of future topics so I'll create one specific for cricket.

[@lovetheentertainment9949]

@@DatabaseStar I hope it will be your next video.

[@judaperez681]

The video I was looking for

[@DatabaseStar]

Good to hear!

[@hodadisbirhan652]

The Quize should be related with the lesson so lesson is attached with module and then module with course , so we can know quize found in the course which is easy

[@DatabaseStar]

That approach can work as well, but it would mean the quiz would only relate to content from that lesson and not a module.

[@walkingradiance9556]

I think the column should be price, not price_usd. There could be a document saying the price is in USD. In addition, you could add this information to a comment on table or comment on column using SQL comment keyword.

[@DatabaseStar]

Great point. It would be better as price as you mentioned and either of the approaches you mentioned could work.

[@Aacadian-xe8kq]

Amazing content, thank you for this upload 👍👍👍

[@DatabaseStar]

Glad you enjoyed it!

[@naveenchandpandey7940]

Great video. Just a question I had about this. How are you capturing the progress of a student for restricting him from going to the next session before completing the current lesson. You have used is_progress_limited but till which point the student is allowed to access is not available i guess. Can you explain it a bit?

[@DatabaseStar]

Thanks! Good question. In the student_lesson table there is a completed_datetime field which captures the date and time that a student has completed a lesson. If the course has a valuen of TRUE for is_progress_limited, then perhaps the application can determine which lessons the student has completed, and then only allow them to complete the next lesson. So, if the student has completed 8 lessons, the application can show that these 8 lessons are completed, and allow the student to watch lesson 9, but lessons 10 onwards are locked.

[@naveenchandpandey7940]

@@DatabaseStar Thank you very much for the quick response.

[@vannakvy9109]

Thanks for the great video.

[@DatabaseStar]

Glad you liked it!

[@walkingradiance9556]

You could store the actual video if you're using BLOBS binary large objects in something like Oracle where its capped at 4GB I think. I'm not sure how KZbin handles their videos if they have them in a database or what. I think MySQL also has BLOBS like large BLOB, medium BLOB, etc. I think it would be a good idea to store the actual video. If the video isn't too long and is usually short say less than 30 minutes, then this could be reasonable.

[@DatabaseStar]

Good idea, yeah it could be stored in the actual column as a BLOB.

[@walkingradiance9556]

Synonyms for module include section, chapter, etc.

[@DatabaseStar]

Thanks for sharing!

[@dway89]

Exactly what was looking for

[@DatabaseStar]

Thanks!

[@julionunes2092]

I think the quiz should be related to the module instead of the course. By relating the quiz to the course, you won't be able to set the quiz in a specific module. One module may have one or more lecture and/or one or more quizzes.

[@DatabaseStar]

That's a good point. We can change it to relate the quiz to a module.

[@theprivterao]

You miss the review table in this Diagram and also course category is also important

[@DatabaseStar]

That’s true, we can add those concepts into this diagram to add those features.

[@yunmulu1659]

Thanks for the lesson

[@DatabaseStar]

You're welcome!

[@abdo_alaa10]

thanks you so much sir for this great explanation , but i have one question here - why we didn't add completed_datetime column to the course table?

[@DatabaseStar]

Good question. We could determine that a course is completed if all lessons in the course are completed, so I didn't add it. But we could add that to the course table, and update it when the last lesson is completed.

[@abdo_alaa10]

@@DatabaseStar thanks sir , now i understand it well

[@longn.8804]

Hello Ben. Thanks for your video. It's very helpful and awesome. I've got a question for you: In each table, there is a PK usually being "id", e.g. the "Course" table has the "id" field and the "Student" table has another "id" field. I totally understand that these "id" fields specifically refer to their corresponding tables. However, does it make duplicate field names (as far as I read somewhere)? Should we keep them identical like that (all "id" fields) or should we make them unique, for example, student_id in the Students table, course_id in the Course table, etc.? Thank you in advance.

[@DatabaseStar]

Thanks, I'm glad you like the video! Good question. The main problem I see with having all fields called "id" (like I usually do) is that if you select from multiple tables, then there will be many ID fields shown and it can be hard to see what the ID represents (e.g. is the lesson ID or the course ID). You can use column aliases to work around this. Using a more descriptive column name, such as student_id or course_id as you suggested, is another approach - and I think it's probably the better approach as it's clearer what it represents in large queries and it matches the foreign key names. I didn't use this approach in this design but perhaps I should in future designs. I would suggest being consistent though - pick one approach and use it throughout your design.

[@longn.8804]

@@DatabaseStar Thanks for your explanation! Appreciate it!

[@mikhailsandigan1424]

Please make for an insurance website it would be really helpful.😊

[@DatabaseStar]

Good idea!

[@omauret]

Your video is excellent; it's exactly what I was looking for. Currently, I'm working on an e-learning platform, and I'd like to know how to create the admin section and a table for the teacher. On my platform, there are paywalls to allow students access via a monthly or annual subscription. How can I develop this section, and what suggestions do you have to implement it?

[@DatabaseStar]

Thanks! I don't have any specific suggestions, but I would say you can try to list down your requirements as sentences and then update the database design. For example, "a course has a teacher, and a teacher can teach many courses". This may mean you have a teacher table, and a foreign key from the course table to the teacher table.

[@CynicalSaint48]

Thanks for the videos! Question I have never been able to find an answer to: Many simple tables versus Few complex tables. In this design, a 10 question quiz would have a Quiz table, 10 question tables, 10 correct answer tables, and 30 wrong answer tables. This results in 51 total tables in the database for a single quiz. Alternately, the quiz table could contain fields for the question id, question text, correct answer and 3 wrong answers, and then that table would have 10 entries. I am curious whether there are benefits of one or the other, or if it is just down to preference? Thanks again!

[@DatabaseStar]

Good question! If you have a quiz with 10 questions, you would only need the three tables: one for quiz, one for quiz_question, and one for quiz_answer. Each table should usually represent an object or entity, so you shouldn't need 10 different question tables - you would have 1 table for all 10 questions. Same for answers: you could have 1 answer table to contain all answers, and an indicator to show if it is correct or not. Generally fewer tables is better, but there needs to be enough so that the data quality is maintained and it's easy to make changes in one place.

[@CynicalSaint48]

@@DatabaseStar Thanks, I follow now. Think I was just a bit thrown by the Lucid representation of the tables. Thanks for answering the many versus few tables question too!

[@celloucisse7548]

Million thanks to you for this video. For sure, I haven't really understand the concept of "number" and "course_number". would you like to me make clear for me plz ?!!

[@DatabaseStar]

Sure! Those numbers are to be displayed on the screen, so the person taking the course (the student) can see things like "Lesson 4" or "Quiz 2". They can also be used to sort the list of lessons or quizzes.

[@farhadeviltrg6116]

Nice job👌

[@DatabaseStar]

Thanks!

[@kane_lives]

Glad you're still making the videos, Ben. Happy to leave a comment just to help you with YT's algorithms.

[@DatabaseStar]

Thanks! Yeah still making videos, and will be for a while I think!

[@jsnow07grad]

very helpful

[@DatabaseStar]

Thank you!

[@gershonsamwoode2334]

Amazing. Thank you

[@DatabaseStar]

Glad you liked it!

[@DuyNguyen-ti1ky]

How can I get the pdf file which contains full content and diagram?

[@DatabaseStar]

It's on the page mentioned in the description: www.databasestar.com/dbdesign/

[@Jackolltradez]

I have a question please, wouldn't that join table have a primary key?

[@DatabaseStar]

Yes, the joining tables could have a primary key to ensure they can be easily referenced and ensure they are unique. This could either be a combination of both FK columns, or a separate ID column.

[@Jackolltradez]

@@DatabaseStar Thank you.

[@walkingradiance9556]

Does the number go up or cycle? For example, if you have a module/section with 5 lessons, then a module/section with 4 lessons, would the lesson's be numbered 1 2 3 4 5, 6 7 8 9, or 1 2 3 4 5, 1 2 3 4?

[@DatabaseStar]

I think it would go up and not cycle, but it would work both ways and depend on who would populate the data for the courses.

[@walkingradiance9556]

small thing--I think it's spelled enrollment, not enrolment.

[@DatabaseStar]

Thanks, yeah in American English it’s enrollment but in Australian and British English (I’m in Australia) it’s enrolment.

[@sumonsarkar791]

❤

[@DatabaseStar]

Thanks!

[@viveksankapal5877]

my project is online learning platform give me database

[@DatabaseStar]

That’s what this video is about!