Database Design for School Students for an Entire School

[@DatabaseStar]

Want to improve your database design skills? Get my Database Design project Guides here (diagrams, explanations, and SQL scripts): www.databasestar.com/dbdesign/?

[@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 😊

[@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!

[@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.

[@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

[@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! 😀

[@gilgameshgaming4210]

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

[@DatabaseStar]

No problem, glad you liked it!

[@Chromosome11]

After a while finally i find this Gem.

[@DatabaseStar]

That’s great!

[@coolingvexation3226]

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

[@DatabaseStar]

Glad you like them!

[@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.

[@-.-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

[@djukic921]

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

[@DatabaseStar]

Awesome! Glad to hear it.

[@h-4-y]

do you have the requirement statement ? so that i can do it myself and compare with it your design

[@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!

[@OmidMahdawi-pg6wg]

So much help was given. Thanks a million ❤❤❤

[@DatabaseStar]

You're welcome

[@AbenezerHaymanot-k8i]

okay perfect explanation but what i could do if i want to create a section for a classes, let say for grade 1 making tow or three sections like Grade 1-A,Grade 1-B,Grade 1-C .....etc, in my country classes are grouped into Sections

[@DatabaseStar]

You could expand the database design to add that concept for classes.

[@ULE13]

Would it work by just assigning the same name to the classes and appending the A, B and C?

[@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

[@paywand2959]

This is education.... thank you sir

[@DatabaseStar]

You’re welcome!

[@barakadewise7200]

The video is awesome and very usefully thanks

[@DatabaseStar]

Thanks, glad you like it!

[@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.

[@e-polk]

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.

[@e-polk]

@@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?

[@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.

[@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.

[@GORDONORIMBA]

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.

[@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.

[@اسمايلي-ذ7ه]

The image link in the description does not work. Is there another solution?

[@DatabaseStar]

Oh that's strange, I just checked it and it works for me. Does this link work? dbshostedfiles.s3.us-west-2.amazonaws.com/dbs/erd_school.png

[@saranshkhulbe3875]

Legendary 👏

[@DatabaseStar]

Thanks!

[@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.

[@astorgadexterpelaez3502]

really helpful video thanks!

[@DatabaseStar]

Glad it was helpful!

[@risnandianggara]

would you give me the requirement for my refrence please

[@DatabaseStar]

I believe they are listed early in the video.

[@MrTrollland]

why cant guardian_type and levels be enums?

[@DatabaseStar]

They could be, but you have more control over separate tables and they can appear in drop-down lists. I created a video about enum vs lookup table here: kzbin.info/www/bejne/eZy1k5p6eZydhK8

[@-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.

[@taslimsuman]

I love your channel

[@DatabaseStar]

Thanks!

[@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.

[@havefun5519]

Thx, really great tut for me.

[@DatabaseStar]

Glad you liked it

[@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.

[@anelembanga6061]

Very informative, thanks.

[@DatabaseStar]

Glad it was helpful!

[@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.

[@SelvamSelvam-qe3wf]

Which software is used

[@DatabaseStar]

I use LucidChart for this.

[@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

[@اسمايلي-ذ7ه]

Thank you very much

[@DatabaseStar]

You're welcome!

[@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.