Page 1 of 1

advice with schedule table structure.

Posted: Mon Jul 30, 2007 4:46 pm
by lafflin
Hello, I am very new at MySQL and PHP, I'm not quite all the way finished my first book and I'm kinda trying to jump right in.
I'm making my first DB, which in comparison to most of what I read in these posts should be pretty simple.
In designing my table layout I'm kinda at a standstill as I can't really figure out how to create a schedule table, or more likely a set of schedule tables.

My DB's function (this part of it) is to create several classes in which to place students, each class potentially has two or more times throughout the week, and some students can be in multiple classes.

I'm not looking for code or specifics, just ideas on table structure.
I know that this seems simple to many of you, but I just can't seem to figure out how to structure it correctly. Thanks in advance for any advice given.

Posted: Mon Jul 30, 2007 5:05 pm
by nathanr
not sure as to the columns but

Classes (class info)
Schedule (timing + the ClassID)
SheduleStudents (link a student to the schedule, shouldn't need anymore than 3 cols, a primarykey, sheduleId and studentid)
ClassesStudents (to link a student to the class, covers the void shedulestudents doesn't cater for)
Students (all students)

SheduleStudents and ClassesStudents are Assosiation/link tables

Posted: Mon Jul 30, 2007 5:15 pm
by boo
I would also have a resource table where each resource would be the rooms that the class would be in.

And one more to link the resources with the classes this way if classes can be in different rooms you are all set

Posted: Mon Jul 30, 2007 5:22 pm
by nathanr
indeed, all the data not covered would need normalised, I'm sure there'll be more than just rooms to concider - have a quick google for database normalisation and see if you find soemthing on 3rd Normalisation, should cover your needs and get you doign ti right first time..

Posted: Tue Jul 31, 2007 9:22 am
by lafflin
Nathanr, that is right along the lines of what I was searching for. There is not mulitple rooms, so that's not really a consideration, however there is multiple teachers, but that's not confusing and I'm not worried about it at the moment.
I wasn't able to see that I might need more than three tables. I am still a bit confused on the StudentsClasses table.
should it be only StudentID and ClassID? plus a primary key.
Oh, I think I see it now! Thanks alot.