Students Database structure for high school help

[AlefAelol]

Have you tested your model with test data and scenarios --- on paper?
You have to design and test and adjust the model based on your requirements. Do not accept the model I provided as anything more than an attempt.

Look at some of the Student data models from DatabaseAnswers.org
https://www.google.com/search?q=dat...tudents&ie=utf-8&oe=utf-8&client=firefox-b-ab
http://www.databaseanswers.org/data_models/student_rosters/index.htm
http://www.databaseanswers.org/data_models/student_marking/index.htm

I browsed the whole database models at the school and student part. No one of fit to my school system.

 

[AlefAelol]

Have you tested your model with test data and scenarios --- on paper?

Can you show us your list of detailed requirements/business facts?

I posted my all requirements,and your model helped me a lot. I just want to extend you model to make it work the way I want. Again, I am really new to idea of using relationships with many fields for the same table. Just have a look at my captured picture again and tell how to fix the model. Many thanks

 

[jdraw]

The free data models at DatabaseAnswers .org are for guidance only. They identify the typical things and relationships generally. They are a starting point to be edited -add things, remove things etc. But it is intended to give you a starting point. None of those models represents an application. These are database structures (tables and relationships); and are independent of your forms and queries.

Perhaps, now that you have "experimented" with the tables and requirements, it is time
to redraft the model and take scenarios with your newly discovered details and retest/stump the model.

Each table could have an autonumber PK (surrogate/artificial key) and remove the use of multiple fields in the relationships.

Here are a couple of models from other posts showing similar subjects. They re samples of how some topics/requirements could be modeled.

 

[AlefAelol]

The free data models at DatabaseAnswers .org are for guidance only. They identify the typical things and relationships generally. They are a starting point to be edited -add things, remove things etc. But it is intended to give you a starting point. None of those models represents an application. These are database structures (tables and relationships); and are independent of your forms and queries.

Perhaps, now that you have "experimented" with the tables and requirements, it is time
to redraft the model and take scenarios with your newly discovered details and retest/stump the model.

Ok,I really appreciate your help. Do you have any valid links or resources to get ride of relationships with multi fields? something like in the captured picture

I want know more about it.

 

[jdraw]

This link re Natural/Surrogate keys may be helpful --found it via Google.


Please post or point me to your detailed requirements.

 

[AlefAelol]

This link re Natural/Surrogate keys may be helpful --found it via Google.


Please post or point me to your detailed requirements.

Many thanks for the link. May I know what other requirements you want to know please?

 

[jdraw]

I would like to see the latest list of requirements, your latest model,
some sample data and test scenarios for testing the model.

You may get some ideas from the links at the bottom of an open thread under the heading SIMILAR THREADS.

 

[AlefAelol]

I would like to see the latest list of requirements, your latest model,
some sample data and test scenarios for testing the model.

Sure,
1- My school include 6 levels (level 1 to 6). Each level can divided into A, B,C ..etc depending on the amount of the students at each level. for example, Level1A, Level1B, Level2A, level2B,Level2C...etc.

2- One level spends one academic year to finish.

3- Students can join the school at any level, and they go to the next level if they pass the current level. So, if a student join the school at level1, he/she go to level2 after pass all courses.

4- A student can fail two years at each level, otherwise he/she will be banded.

5- Each level has it courses and credits, all courses are mandatory(optional courses not allowed) for the students to take.

6- Each course goes through six exams, three exam at each semester. (Note, each academic year is divided into two semester ).
7- The final mark for each course is calculated as

(SumOfSemester1Exams+SumOfSemester2Examss)/2

8- If the final grade of a course is above 40, students considered as passed at that course.

9- If a student pass all courses of the current level, then that student is qualified to the next level. Once the student pass level6, then student will graduated.



-------------------------------------------------------------------------------------
That was all about my school system in term of the requirements. Now, lets go to the data modeling. Actually, I still having your draft model and works in fine but with some issues. Well, lets talk about your model and what I have done as a database tables and forms based on your suggested model.



1- A main from and subfrom for LevelCourse entry. So, for each level in the main form, a number of courses and their credits are inserted. After a user fill all levels, the table below is created:

 LevelID       CourseID       Credit
     1                    1                  2
     1                    2                  2
     1                    3                  4
      ...........................................
      ...........................................
      ...........................................
     2                     1                1
     2                      5               3
     2                      12             4
    ............................................
    ............................................
    ............................................

and so on for the rest of the levels and courses.





2- A form with a subform and combo box, the main form is for Students information entry and the subform for the StudentLevelCourse registration. The first step the user do is to fill the students information, then the user select LevelID from the combo box, in the beforeUpdate event of the combo box, I added the following code

Private Sub cmboLevelID_BeforeUpdate(Cancel As Integer)
Dim strSQL As String

    strSQL = "INSERT INTO StudentCourseLevel(StudentID, LevelID,CourseID,Credit) " & _
             "SELECT " & StudentID & ", LevelID , CourseID, Credit " & _
             "FROM QueryCourseLevel " & _
             "WHERE LevelID = " & Me.Text19.Value

    CurrentDb.Execute strSQL, dbFaiOnError

    Me.StudentLevelCourseSubForm.Requery
End Sub

Where StudentCourseLevel table is the record source for StudentLevelCourseSubForm. That makes StudentCourseLevellook like :

That worked fine, but it fail when a student fails at a specific level, because these records would duplicated at that table since there is no reference for the academic year to make the records distinct.
I stopped at here and no more move on my database. That is all that have done with me PLUS some tries as below:

But, I failed because I can't insert data to the table StudentsCourseLevel. Please push my work into the right way by your valuable helps

 

[AlefAelol]

I would like to see the latest list of requirements, your latest model,
some sample data and test scenarios for testing the model.

You may get some ideas from the links at the bottom of an open thread under the heading SIMILAR THREADS.

This a sample of my access database file. Sorry for the bad organizing of the forms.



View attachment Test.accdb