I'm working on solution for vocational school. Currently I'm struggling with database structure/setting up relationships.
Students in our school are learning for three years. Student is associated to fixed profession that he or she is learning.
Each student during school year has to take part in professional course accordingly to his profession.
So far we allow to study in one of twenty professions.
Every profession has 3 grades of course (one for first grade students, one for second grade students and one for third grade students).
Therefore in one school year there can be up to 60 professional courses. This courses takes part outside of school in proper institutions. Every course is linked to 3 institutions (one that organise, one that is actual place where course takes part, and one who has dormitory).
If student wish, there is possible for him to be accommodated in dormitory during professional courses. Courses usually take 4 weeks.
As many institutions are organising such courses, in every year we can choose different institution to carry out course.
And every year start dates, end dates of courses and pricing are changing.
So far i managed to do my thing by creating solution for current school year. That solution is no longer sufficient. Now I need to add history/tracking info. I'm puzzling with this and can't get a clear solution for that.
So far i got this tables:
Institution (this one has 3 TO: Organiser, Place, Dormitory)
Relationships look like this:
Student >– Class
id_Class = id
Student >– Course
id_Course = id
Student >– Profession
id_Profession = id
Course >– Organiser
id_Institution_Organiser = id
Course >– Place
id_Institution_Place = id
Course >– Dormitory
id_Institution_Dormitory = id
Course >– Profession
id_Profession = id
id_Course is filled with script that evaluates id of proper course based on profession and class with Case () function.
After puzzling i came to something like this:
Student –> SignUp <– Course
In Students table:
and more information
ifAccomodated (Yes/No field – yes if Student was accommodated in dormitory)
grade (I, II, or III)
Is it a good approach? I would like to assure before I will start thinking how to populate this tables.