AnsweredAssumed Answered

Relationship problem

Question asked by macc on Jul 28, 2016
Latest reply on Jul 30, 2016 by beverly

Hi everyone.

 

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:

Student

Proffesion

Course

Institution (this one has 3 TO: Organiser, Place, Dormitory)

Class

 

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:

id

and more information

 

In SignUp:

id

id_Student

id_Course

ifAccomodated (Yes/No field – yes if Student was accommodated in dormitory)

 

In Course:

id

id_Profession

grade (I, II, or III)

schoolYear

startDate

endDate

id_Organiser

id_Place

id_Dormitory

courseCostPerStudent

dormPricePerStudent

 

Is it a good approach? I would like to assure before I will start thinking how to populate this tables.

Outcomes