Database Design for School Students for an Entire School

📝 Get my Database Design Guides to many different sample databases: www.databasestar.com/dbdesign...
🎓 Learn how to design an effective database and create it using SQL: databasestar.mykajabi.com/edd...
Databases for schools where students change grades each year is a little more complicated than your average "university database design".
In this video, you'll see me design a database for a school that allows multiple levels, such as a primary school or secondary school. We will look at 16 different requirements and slowly design the database to meet each of them.
Check out the link below for an image of the entire completed design.
⏱ TIMESTAMPS:
00:00 - Intro
01:14 - Req 1: students
02:24 - Req 2: parents and carers
04:31 - Req 3: school years
05:27 - Req 4: terms
0614: - Req 5: year levels
08:01 - Req 6: classes
08:38 - Req 7: subjects
09:13 - Req 8: departments
09:55 - Req 9: teachers
10:43 - Req 10: teacher details
11:15 - Req 11: classes and terms
12:04 - Req 12: classrooms
13:09 - Req 13: class times
13:54 - Req 14: multiple periods
14:35 - Req 15: student scores
15:25 - Req 16: score grade mapping
16:59 - Further requirements
🔗 VIDEO LINKS:
Final ERD of this database: dbshostedfiles.s3.us-west-2.a...

[@jasonkelley6185]

This is my new favorite youtube channel. I'll be binging all your videos. Very concise. You aren't saying "uhm" every other word and stumbling over the ideas. Very clear. Great voice! This is how it should be done. Thanks you!

[@DatabaseStar]

Thanks for the kind words! I try to keep my points focused and well explained so I don’t confuse the viewer and waste their time. I’m glad you’ve noticed that 😊

[@-.-smile]

Amazing videos, Ben! Keep up the great work. I’ve been using your videos to upskill my Oracle SQL skills that I use at work. Many thanks!

[@DatabaseStar]

Great to hear! Glad you like the videos and that it is helping your SQL skills

[@gilgameshgaming4210]

Thank you for the time and effort you put into this awesome explanation❤

[@DatabaseStar]

No problem, glad you liked it!

[@anmarm.9487]

This was very helpful. Still interested in more of this database, such as fees, payment installment, attendance, quiz scores, maybe authentication for guardians, maybe tables for notes or messageboard, or even students homework, imagination is endless also few queries to pull useful real life cases. I like your tutorials very helpful, just wish they will go bit more advance.

[@DatabaseStar]

Great ideas! I think they could all be added to the database design. I'll keep this in mind for future designs - especially sample queries.

[@oracle_professor]

Thank You!, I'll recommend the video to my students!

[@DatabaseStar]

Thanks! 😀

[@epicgameryt4052]

Really awesome video! Keep up the great work. I've worked on multiple school management systems, and this seems like a really smart approach. I mostly work in the Netherlands, so we don't know things like terms, but great work.

[@DatabaseStar]

Thanks a lot! Glad you liked the video.

[@medilies]

I worked on a SMS before and this gold. There will be one inconvenience with OOP which us the entity named class. The system can also go to supporting school year expenses and having students guardians and teachers as users

[@DatabaseStar]

Thanks! Yeah it can be tricky mapping entities to database tables. Thanks for the comment on expenses and users, they are great ideas.

[@djukic921]

This video was very helpful for my project. Thank you very much for this!

[@DatabaseStar]

Awesome! Glad to hear it.

[@coolingvexation3226]

Designing databases is so fun, these videos are very helpful.

[@DatabaseStar]

Glad you like them!

[@TheInternetFan]

best thing I did last year for my training was subscribe to this channel. great content man!

[@DatabaseStar]

Thanks! I’m glad you like the content and have subscribed!

[@astorgadexterpelaez3502]

really helpful video thanks!

[@DatabaseStar]

Glad it was helpful!

[@Chromosome11]

After a while finally i find this Gem.

[@DatabaseStar]

That’s great!

[@barakadewise7200]

The video is awesome and very usefully thanks

[@DatabaseStar]

Thanks, glad you like it!

[@mitchell2769]

thank you for another fantastic video! In the final schema, the one thing I don’t understand is which start_period_id and end_period_id in the Class table are both linked to the PK in the Period table. Shouldn’t they be linked to the start_time and end_time fields in the Period table? (I’m new at this, haha)

[@DatabaseStar]

Glad you liked it! Good question. The reason is so that all the classes can have the same start and end time. The start and end times are attributes of a period, and not a class. For example, "Intro to Physics is in period 1 so it starts at 9AM", rather than "Intro to Physics is at 9AM". This also means that any class in period 1 will start at 9AM, which can help with timetables. But this is an assumption for how periods work from when I was at school. Different countries may work differently. Having a start and end time on the class table could also work.

[@osmanhamashool6396]

hey! This is awesome, we would appreciate if you follow up another video with the rest of the requirements.

[@DatabaseStar]

Thanks! Sure I can do a follow up video

[@asdfasdfuhf]

Question for 7:20: You say: "We know that students are in one level at a time, but they can be in more than one level during their time at the school. So, we'll need a joining table, as this is a many-to-many relationship". I would say this is a many-to-one relationship: One student can be at EXACTLY ONE year_level at a time. One year_level can be shared amongst MANY students. Thus, Many-to-One. Is my logic flawed?

[@DatabaseStar]

Good question! Yes, it could be described as a one to many relationship. However, for the data in the table, there would be a record for student 1 in year level 1, then student 1 in year level 2. Only one record would be considered the active or current record, but we would still need to store both in the table. I think the main point here is that it's storing data over many years. If it was only storing the current year, then it would be a one to many and we wouldn't need the joining table.

[@soj_afk]

Thank you for the question and the response! I was confused about this as well thinking about how one student would be in multiple years!. I figured someone else would have noticed and decided to check the comments for a better response! This is very helpful!!! I definitely would take more videos on database structure on your channel! Thank you for the first class lesson!!!

[@kirilsivanovs1929]

Good day! I can`t understand... If we are going to store information about Students and Levels/Years as N:M, that why we are not gonna do the same trick with Teacher and Class?@@DatabaseStar

[@AhmedNabil-pq6eo]

Great job and well done, what is the name of the application that you used to draw the ERD, appreciated it.

[@DatabaseStar]

Thanks! It’s called LucidChart.

[@hiluu]

Thanks for your video! I have a question that how do we design if a class is held in many rooms and it has many periods on different days of week?

[@DatabaseStar]

Good question. If that is a possibility you would need to have a joining table I believe, to capture the possibilities or combinations of many classes and many rooms.

[@user-lc8mf5cs8t]

Great video! Could you give me some pointers on how to capture Attendance using this same database design?

[@DatabaseStar]

Thanks! I would suggest thinking about the kind of things you need to capture for attendance, and then how it relates to each of the existing tables. For example, you may want to capture attendance for each student within each class for each time the class happens. So you may want an attendance table to link to the student, the class, and store a date, and perhaps an indicator of "attended" and "did not attend". You may also want to capture whether or not the student has already let the school know in advance, e.g. "away with a reason", to tell them apart from students who did not attend but nobody knows why.

[@user-lc8mf5cs8t]

@@DatabaseStar Thank you. Yes, that's exactly what I want to do. How would I set up my relationships between the three tables: Student, Class, and Attendance?

[@kiethuynh2820]

Awesome video! but i wanna ask you about the "guardian_type_id" field, I think that this field should be set in "guardian" table instead of "student_guardian" table

[@DatabaseStar]

Thanks! Yes the guardian type field could also go in the guardian table and that could work. I added it to student guardian because it allows a guardian to have one type for one student and another type for another student. But that may be rare so it could be unnecessary.

[@kiethuynh2820]

​@@DatabaseStar Thanks u!

[@anmarm.9487]

Please. Could you elaborate on the relationship rules, example if student get removed which data get removed or changed to null. ON DELETE and ON UPDATE. That will be great...

[@DatabaseStar]

Good idea. Generally you would either remove the related IDs or set them to NULL, depending on what rules you wanted to implement. Alternatively you could have a new column in the student table for "active", and if you need to delete a student you would set active=0 instead, so you kept the data. This concept is called "soft delete" and is common.

[@pulkitsrivastava6132]

Why we made separate table for guardian type? The guardian type can be just stored in student_guardian table

[@DatabaseStar]

It could be stored there. However, having a pre-defined list of guardian types makes it easier to select from a list, and also ensures that only these values can be used. If it's free text then it's easier to put in the wrong data.

[@DANNYEL20122]

Please can you do this but for colleges

[@DatabaseStar]

Sure, I think this design should work for colleges as well. The year level could include rows for different years of college.

[@user-mz1sf7iy1p]

Hi Do you have a template for school management system on access? Kindly share if any.

[@DatabaseStar]

Do you mean a template on Microsoft Access? If so, then no, as I don’t use access. But you can use something like this video to start.

[@-0-__-0-]

Does this cater to students of all year levels and college students? I'm expecting that the diagram would work on Elementary and Senior High School students, but may not work in college as it is quite different. But knowing the moodle is capable of doing it, maybe I'm wrong.

[@DatabaseStar]

You’re right, it may not work on college students. One reason is that it doesn’t include courses or majors and the ability to have different subjects within them. But the design could be expanded for that I think.

[@-0-__-0-]

@@DatabaseStar Thank you.

[@codingcambodia]

please make on online course database design where each lesson also contains quiz. Student needs need to complete the quiz of the lesson to be able to move to the next lesson

[@DatabaseStar]

Good idea! I'll create it in the future.

[@codingcambodia]

@@DatabaseStar thanks

[@Dhirajkumar-fb3to]

Is it same for noSQL database mongoDB

[@DatabaseStar]

The concepts should be the same, but the implementation may be different. I don’t have any experience with nosql.

[@GenjaOrigins]

How can i learn to design a database based on requirements? i know sql commadns but i dont know how to desin a database teh shape etc

[@DatabaseStar]

I've got a video here that shows you how to design a database from an idea or requirements: kzbin.info/www/bejne/a4PThqB6etihZ80

[@GenjaOrigins]

@@DatabaseStar I had an interview and i got rejected cause they asked me to change my database based on some different requirements and i dont know what to study.

[@satishkumasi2326]

Hi sir, is there any way for asking questions to you on your paid courses?, how to contact you if I have questions before enrollment. Could you please provide the contact

[@satishkumasi2326]

Contact form does not work. Could you help

[@DatabaseStar]

Hey, yeah you can contact me with any questions you have at my email: [e-mail]. What issue are you having with the contact form?

[@sasidharnaidu4507]

Going by the recent happenings in the USA, you should have a separate table for storing the Gender information😅😅

[@DatabaseStar]

Yeah and I think a lot of places may do this, so that Gender can be a drop-down list.

[@sudowoodo8919]

Naming tables in singular? Not a good start.

[@DatabaseStar]

Yeah singular vs plural is a common debate in the database world. Do you prefer plural?

[@sudowoodo8919]

@@DatabaseStar just messing with you. I do prefer plural though.