Mysql – School database design

database-designMySQLnormalizationphpmyadmin

I am trying to design a database management system with my PHP and intermediate MySQL knowledge, keeping normalization in mind.

These are the assumptions:

The school has many students from grade 1 to grade 6.
Each grade is divided into classes (e.g. we have grade 1 A, grade 1 B, grade 1 C) due to the class size.
Grade 1 to grade 3 offer the same subjects, but from grade 4 to grade 6 users can select which courses to offer.
Many teachers teach ONLY one subject BUT can teach MORE THAN one grade.

As an intermediate programmer, it all got twisted.
I was able to create the following tables and columns

students{(id),(name),(name),(parent_id)} -student details

parents{(id),(name),(email),(phone),(address)} - parent details

teachers{(id),(name),(email),(subject_id)} - teachers details

subjects{(id),(name),(description)} - subject details

attendance{(date),(status),(student_id)} - attendance of student with id of student_id,
                                           status in boolean TRUE = present, FALSE=absent

This is how far I have gone.

Now I want to show a table where a student can be linked to all the subjects he/she is taking and a teacher linked to all the grades (no need to specify classes) he is teaching. How can I do that?

I will appreciate it if I can be pointed in the right direction.

Best Answer

Homework questions like this will generally not elicit answers with code. Stack Exchange is not a place where you can come to get others to do your homework for you. However you have only asked for pointers of where to look, so to that end, I will inform you that what you are looking for is called a many-to-many relationship, and requires a third table that maps table 1 to table 2. Usually, this third table just has two columns, each containing the primary key of one of the main tables, and the primary key of this table covers both columns.

Read up:

Related Solutions

Database schema design for school system

To your specific question of (easily satisfied) plausibility, the answer is yes. Since you seem to be looking for a broader critique. You seem to be mostly on the right track, but a few things are making your description overly complex.

It looks like your Classes table is more like what I would consider a course to be and your departments_teachers_and_classes is more of what I would expect a Classes table to be. A course would be Math, but a class would be the Math course taught by a particular teacher during a particular school term. A class is like an instance of a course.

To carry this change through, your departments_teachers_classes_and_students could simply be ClassStudents.

It seems as though you are using the department concept on several different levels. You should decide where the department belongs and stop referencing it everywhere else. You've said that a teacher can teach for multiple departments, so we can't put the department in the teachers table. This leaves Classes and Courses. Whichever you decide, it need only have a foreign key to the Departments table (unless you decide that one can be in multiple Departments). This eliminates the Departments_and_teachers table.

Teachers_and_Students could be replaced with a teacher foreign key in the Classes table and students/classes foreign keys in a ClassStudents table.

In short, for the portion of the design you have described, you probably need the following tables:

Departments
Teachers
Students
Courses
Classes
ClassStudents
Terms

Sql-server – Database Design Confusion

Creating independent databases will create the risk that as data structures change with the application, structural changes (and any associated data migrations) must be propagated to all of the databases. This can get tricky. A school_id in the tables that need it will simplify things in the sense that you only need to maintain one database.

Multiple databases would make more sense if you are going to deploy the application to physically separate clients (such as if the application runs independently on a server in the school), but will make maintenance more difficult.

Another place where it might make sense to have a separate database for each client would be if you know there will be slightly different versions of the data structures for each client. Of course, changes to common structures would still have to be propagated to all variant databases, and then you have to ensure that common patches/upgrades to the application work properly with the variants.

In general, I'd try to keep it all in one multi-tenant database, unless there is a very good reason to split it up.

Related Question