Hello I am new here

I am just programming a little lesson planner to keep my daily to-do stuff together.

If you regard the 1th attached image done in excel you see the columns names which might be used as table/field names too just depends on designing a proper normalised database design. That first Image is the template of the view my programm should look like later...

The other image shows the relating tables I have done so far and which are important that you know them:

the red marked tables are important:

FACH = subject
SCHULKLASSE = class

I have already 2 tables for "subject" + "class" called FACH + SCHULKLASSE. Now I probably need another 2 tables called "period" + "homework" or I create a table "lessonplanner" which keeps the field homework.

The question now is how can I use the existing 2 tables and connect them together with the period + homework?

Hmmm... Based on the model you've presented, I'm not sure how you'd attach the concept of homework.

I see Fach as being a parent of Klausur. Fach might or might not be a parent of Shueler, depending on how your school thinks about that relationship. In a simple model, I would see these as a direct parent/child relationship so the fach_id could go directly into the child tables. In a more sophisticated model, the joining table (such as shueler_fach) with beginning and ending dates would be better because it would allow for both multiple relationships and relationships that change over time.

You need to think about how you want to deal with the abstract idea of Klasusur (which your existing table does nicely), versus the more concrete case of a specific instance which would have attributes like instructor, location, etc. This is where I'd expect to tie the idea of homework.

Once you work that out, I'd create an Arbite table that included assigned date, due date, and similar attributes.

Does that help, or just confuse things?

-PatP

Hm... it rather confused me well every SCHUELER has Many FACH giving the chance some pupil attend ethics and others religious education. So I do not want to delete that relation.

Could you tell me more of your concrete case?

What is an arbite table? google did not help me.

Would you mind drawing a rough design of the tables you have in your head?

I am one of the more visual humans - images say more than words ;-)

What creates the relationship between Fach and Shueler? Taking a class, declaring a major, or something I haven't thought of yet? If the relationship depends on taking the class, then I can't see any need to store this information separately. I don't understand this relationship well enough to explain how I'd model it yet.

Arbite was a typographical error on my part. I meant Arbeit, but my fingers got ahead of my brain!

Can you describe what the Klausur.gewichtung represents? If Klausur is completely concrete, then this might relate to the student. If it is a bit more abstract and it explains only one instance, then it could relate to the instructor. If Klausur is completely abstract as I originally thought, then I'm not sure how this might relate.

-PatP

a pupil(schueler) has many subjects(fach) like Math, English, Latin, Ethics etc...


a major doesnt play a role here. This programm I wanna do is not meant for calculating reports and wether a pupil advances to a higher class or not. Its for my use having a structured organisation and overview what happens ;-)

If you speak of the Schueler : Fach relation -->
- ONE Schueler has MANY Fach(subjects)
and
- ONE Fach is attended by MANY Schueler

googling this: arbeit table database --> no results ? what is arbeit? you have a synonym for it?

Klausur can be:
- test
- class excercise
- the way you present your excercise book
- oral marks

the field "art" means sort of Klausur like "test no.1" or "2nd oral marks" art = kind of Klausur

Klausur.gewichtung means how much one Klausur counts like 50 % a class excercise and a small test only 25 %.


this confuses me hihi. Who is the instructor in my diagramm? A teacher does not play any role here.

I hope my explanation did help you a bit.

I'm sorry, I don't understand your problem well enough to help you model it.

-PatP

Hello Pat,

probably tomorrow I will show you an english version due to the localisation of my programm that might help you to retrace my thoughts ;-)

ok it always gets later like i want

In den SCHOOLDAY_LESSON + SCHOOLCLASS_SUBJECT tables I have used now natural composed primary keys instead of surrogate keys I used before in the above tables. For example a surrogate key in the SCHOOLCLASS would allow to create 2 classes like "10c" but this must not be possible, so the schoolclass_name is the primary key here.

If you have any critics or see redundancy please let me know

I have totally reworked the diagramm: check image 1

If the diagramm/database is normalized properly it should be able to do some join queries and get data like this: check image 2 (the important columns are colored - it should be able to have the same schoolclass_subject combination in the same period at different dates)

Maybe the lesson_id AND period is redundant...

Thank you for you help!