Hi there, i'm trying to create a class schedule for subjects where students can view what's available and later on book a position in a class.

Here are the images of what i'm trying to achieve: https://imgur.com/a/Ie4XO

I want to retrieve the table name and ID of students that have booked a place in a class from the subject table and show them in the main table in the correct time slot.

Is there any online resouces which can help me achieve this? i have already created the tables in my database, the tables but having issues on how to progress.

Member Avatar

diafol

This is timetabling. Bare bones tables needed are:
Students, Subjects, Classes, Periods, Class_Periods, Bookings

Can you show us the tables you've created and what columns are in each one? What is the schema doe the subject table, for example?

It would help us greatly to see if you're on the right track here.

https://imgur.com/a/s7Dem

Here are the colums for two of the tables i have done so far. The main schedule table and a class.

Do i make use of foreign keys? if so how would i go about doing so that it calls the info from the other table into the main schedule table

Thank you

Member Avatar

diafol

My mistake, I see what you want. This is not exactly a trivial setup if you're a beginner. I'd do this:

Students, Subjects, Classes, Periods, Class_Periods, Class_Students

Foreign keys are a must for this.

I would avoid hard-coding start-times as columns in your schedule table. I would define all your teaching periods in a Periods table:

id | start_time | end_time | day (0-6) | week (if on two week - or more - timetable) | semester (if schedule changes) | year (academic start) | period_label (if req'd e.g. 1 or P1 for 1st period of day)

That's just an example - maybe a little overboard. It takes a bit of time to set this up, although you could write a simple-ish script in something like PHP to fill the data for you. The benefit of this then is that you can query this data to create a select form control to add periods to a class.

So your "schedule" (Class_Periods) would look like:

class_id | period_id (compound PK of both fields)

Just an idea.

can i get the code that you have used for the creation of the tables and joining them which would be most helpful for our project.

What 'code' are you referring to? Which language?