I have been coding using just NoSql mongodb databases but friends told me I won't go far as a backend if I don't understand things from relational. just started learning SQL and used August to complete a course on SQL from udemy. This is my first attempt at modelling a school database. Can anyone check and point me in the right direction, errors, what I could have done better and so on.
Note: The link between student and class it's not suppose be one to many, should be many to one instead, I made an error with the line direction.
Then there is a missing many to one illustration between student and arm too

Shugadpuna:
I have been coding using just NoSql mongodb databases but friends told me I won't go far as a backend if I don't understand things from relational. just started learning SQL and used August to complete a course on SQL from udemy. This is my first attempt at modelling a school database. Can anyone check and point me in the right direction, errors, what I could have done better and so on.
Note: The link between student and class it's not suppose be one to many, should be many to one instead, I made an error with the line direction.
Then there is a missing many to one illustration between student and arm too

You are doing well

hakeemhakeem:


You are doing well

Yeah... But no constructive comments?

Shugadpuna:
I have been coding using just NoSql mongodb databases but friends told me I won't go far as a backend if I don't understand things from relational. just started learning SQL and used August to complete a course on SQL from udemy. This is my first attempt at modelling a school database. Can anyone check and point me in the right direction, errors, what I could have done better and so on.
Note: The link between student and class it's not suppose be one to many, should be many to one instead, I made an error with the line direction.
Then there is a missing many to one illustration between student and arm too

It will nice if your tables indicate your primary key.

4dor:


It will nice if your tables indicate your primary key.

Of course they will when I am actually implementing them.

Hmm

I'm not a DBA. So these are just my opinion from my developer point of view.

1. The '_id' suffixes are redundant. The field names could do without them.
e.g the parents_id field under student should be parents

2. The table name prefix before the id are redundant
e.g the parents_id field under parents table should just be id

3. There should be consistency in your table names, either all plural or all singular. I will go with all plural.
e.g arm should be arms

4. The date and time fields in attendance should be a single timestamp field, call it anything descriptive.. I will call it date_time

5. In my opinion if you have fields in your table that have incremental suffixes, it can be improved. Like the test_1 and test_2 in your graders table. It hurts the scalability of your db. I will have a single test field and store all the tests inside it as JSON.

6. Your student_subject table with its present design will have several rows. For better performance, have a student_subjects table with a student field to store the student's id and a subjects field to store a JSON array of the subject IDs. This way you will have just one row per student.
Personally I will discard that table entirely and put a subjects field under students table and store the subject IDs in that field as JSON


I would like to know if the course you took talked about field naming conventions. I prefer to see db table field names follow the same pattern of my variable names in my code. So I would do firstName instead of first_name... but this is just preference and ease.

I'm no SQL guru , so I'll ask questions instead

I can see the relationship between parent and student table is a one to many relationship.

Looking at the real world, a child/student have two parents (A father and a mother), so I would have gone with a many to many relationship because multiple student can have the same parent.

why did you go with a one to many relationship?

My second question

what kind of school? elementary or high or tertiary?