advice with schedule table structure.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
lafflin
Forum Contributor
Posts: 123
Joined: Thu Jul 26, 2007 6:26 pm

advice with schedule table structure.

Post 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.
User avatar
nathanr
Forum Contributor
Posts: 200
Joined: Wed Jun 07, 2006 5:46 pm

Post 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
User avatar
boo
Forum Commoner
Posts: 42
Joined: Mon Jul 02, 2007 11:30 am
Location: NY

Post 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
User avatar
nathanr
Forum Contributor
Posts: 200
Joined: Wed Jun 07, 2006 5:46 pm

Post 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..
lafflin
Forum Contributor
Posts: 123
Joined: Thu Jul 26, 2007 6:26 pm

Post 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.
Post Reply