How to biuld a coursescheduling database

[Guest]

Hi Everyone

thanks for all the help i have had so far.

as i have mentioned before i have a event management database which with all
the help i have had here now works wonderfully. i am now going to try and be
ambitious and plan to add more functionality to the database. i would like
to be able to manage my course timetables through the same database being
able to input sessions that would be run where and when. i am in the very
basic stages of this and would like some assistance for what my table
structure would be like

i would basically want to be able to print off reports that look like a
timetable for the day

i already have an events table in my database and would be able to get info on

course
date
location

from that table the problem i have is what other atbles do i need

on the report i need

time of session
time of break (could just be a session)
type of session (e.g what is chemo, what is radiotherapy what is cancer)
teacher (who is doing the session) different people will do the same sessions

I will give the time to the session i just need it to be organised on the
report

i hope someone can help, i looked at Duanes surgery planner but could not
understand how i could adapt it so gave up

thanks

Phil

 

[Guest]

I would think your course table should not include the date and location.
You need these tables:

Courses - list of courses
Locations - list of course locations
Teachers - list of course teachers
Sessions - course sessions

The Sessions table contains links to course, location and teacher and has
the date and time.
I don't know what you mean by 'time of break'.
Your report would run off of the Sessions table sorted by date and time.

If you are using this as a session planning tool, you will need logic to
make sure the same teacher does not have conflicting times on different
courses or that given locations are not double-booked with courses.

Dorian

 

[Guest]

Hi Dorian

thanks for your speedy reply my courses table currently holds the dates of
the course and start stop times of the course and locations and i cannot
change that without changing the structure of the rest of the database i also
dont think i need to change this as the venue will not change for the course.

thanks for giving me a table structure

i will need some logic, however i only ever run one course on one day so
doublebooking will never occur.

thanks again

Phil

 

[Amy Blankenship]

So you're saying you'd never run a course more than once?
-Amy

 

[Guest]

I don't think you should be afraid of changing the table structure if the
change results in a better design. I change the table structure of existing
databases all the time, its totally transparent to the users. It sounds like
you might have made the classic mistake of designing the database to attack
the immediate problem at hand without thinking ahead about how your database
may handle future needs.

You are now left with the problem of where to put the teacher data and the
other attributes of the course sessions. They really belong in a separate
table but you already have the course date in your existing table. If you
don't change the table structure, you will be left with a mess.

Dorian

 

[Guest]

No, i run two courses a 3 day course and a 5 day course one on a monday one
on a friday both courses run 6-8 times in a year, as i am facilitator/teacher
for most of the course i cannot split myself in two so there are no
concurrent sessions so therefore there can be no conflicting times or
doublebooked locations

thanks

phil

 

[Guest]

thanks dorian

having thought about it having typed a reply i have started to change the
structure of the table in line with what you have suggested, it appears to be
a more elegant solution to a problem i have asked questios about here before.
the only problem i will have is with relationships as i always fall down
here, i am sure asking more questions here will help sort this out

thanks again

Phil

 

[Amy Blankenship]

If you run the course more than one time and you still want it to be
considered the same course, then you need the date and time of the course to
be in a separate table from the course definition, because after the first
time you run the course you'll need to have another record with the dates
and times.

HTH;

Amy

 

[Guest]

I think you have made a wise decision.

Regarding relationships, what you will end up with will be something like:

Courses - list of courses
CourseID
CourseDescription

Locations - list of course locations
LocationID
LocationDescription

Teachers - list of course teachers
TeacherID
TeacherDescription

Sessions - course sessions
SessionID
CourseID - links to courses table
LocationID - links to locations table
TeacherID - links to teachers table

As far as the individual days and times of each session, I'd create a couple
more tables for that:

SessionDays
SessionDayID
SessionID links to Sessions table
Date

SessionDaySegment
SessionDayID links to SessionDays table
Segment Type (Study or Break)
SegmentStartTime
SegmentEnd Time

Dorian