Results 1 to 6 of 6
  1. #1
    prawer is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2011
    Posts
    3

    Tutor Scheduling Database

    Hello,



    I am a tutor at a university writing lab and have volunteered to work on designing a database to bring our current paper-based scheduling system into the 21st (or 20th ?) century. I worked on access extensively a few years ago but haven't picked it up in a while; nor do I have a great deal of experience with the kinds of forms or logic this might require.

    In any event, I'm anticipating that I would need tables and fields for:

    TUTORS
    Name
    Contact Info
    Hours Scheduled to Work
    Available Hours


    STUDENTS
    Name
    Student ID
    E-mail
    Phone
    Primary Class [that brought them in to the center]
    Additional Field indicating if they are in certain kinds of classes that require particular minimum # of appointments per semester (remedial and writing-intensive courses)
    Appointments attended
    Appointments missed

    SCHEDULE
    Half-hour appointment slots

    I have a feeling that this hypothetical design is rife with the kinds of overlaps that would undermine a relational design. The database I'm thinking of would need to be able to do things like have a form for scheduling that populates a calendar with as many open half-hour time slots as we have tutored scheduled to work for that time; that would have sufficient logic to prevent scheduling students who have missed two appointments without administrator approval; that would (if possible) generate e-mail reminders, or at least lists of e-mails of students who have appointments coming up in 24 hours or less ... and a few other features.

    I realize some of this may have to do with access features and not with database design itself, but I'm hoping someone can give me a sense of whether access is the appropriate solution for our needs, and how I might want to get started. Thanks,

    Prawer

  2. #2
    prawer is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2011
    Posts
    3
    One amendment to this (that seems to complicate things):

    Resources (tutors) don't need to have appointments with particular students, but the number of tutors available at each half hour should determine the number of students we can schedule at each half hour; additionally, each student should, at the time of setting an appointment, be able to request a tutor who is working at that time (although, again, there is no requirement that they actually be scheduled with a particular resource at a particular time in the database).

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  4. #4
    prawer is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2011
    Posts
    3
    Quote Originally Posted by orange View Post
    Hi Orange,

    That looks like a good start but is designed to store student-guardian information. We don't need much in the way of storing student information, and we don't need to store any guardian information; our primary interest is in scheduling.

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Yes, I thought that it might be a little off target, but it was a free template, so had some followers and went through some design.

    Anyway, what you're asking can be done in Access. How many users would involved?

    As for your Tutors, do you have to know what subject/discipline they can "teach"?

    Are tutoring appointments always 1:1 Tutor/Student or 1:Many?

    TUTORS
    Name ....... First and Last
    Contact Info .... phone, email,???
    Hours Scheduled to Work ..... Unavailable for tutoring??
    Available Hours


    STUDENTS
    Name...............First and Last
    Student ID ... from some other Student Info system (in case they have to relate info)
    E-mail
    Phone
    Primary Class [that brought them in to the center]
    Additional Field indicating if they are in certain kinds of classes that require particular minimum # of appointments per semester (remedial and writing-intensive courses)
    Appointments attended
    Appointments missed
    .would suggest these may be in a StudentTutorAppt table

    SCHEDULE
    Half-hour appointment slots

  6. #6
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    Access will work for you. I designed a scheduling system for a Hospital Scheduling their Surgeries and Cath labs. 5 Operating Rooms 2 Cath Labs. I used Access to design the system but ended up moving the Data into SQL Server. System has been in use at the hospital for 4 years now without a burp.

    Now to your design

    In any event, I'm anticipating that I would need tables and fields for:

    TUTORS
    TutorID
    Name
    Contact Info

    TutorSchedule
    TutorID ForeignKey to Tutors
    ScheduleID ForeignKey to Schedule Link to get the appts

    STUDENTS
    Name
    Student ID
    E-mail
    Phone
    Primary Class [that brought them in to the center]
    Additional Field indicating if they are in certain kinds of classes that require particular minimum # of appointments per semester (remedial and writing-intensive courses) This would be a numeric field that defaults to 0

    SCHEDULE
    ScheduleID
    Half-hour appointment slots

    You should have 1 more table that links the Schedule table with the Student and Tutor tables.

    Appointments
    ScheduleID (Foreign Key to get the Schedule Time)
    StudentID (Foreign Key to get the Student Data)
    TutorID (Foreign Key to get the Tutor data)
    Appt_Date (Date of this appointment)
    Appt_Status (Yes/no Field for if the student attended or missed)

    This design assumes the Tutor is available for the same time periods each day. If this isn't true the design will have to be modified to account for it.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Scheduling Presses
    By bbrazeau in forum Database Design
    Replies: 26
    Last Post: 05-09-2017, 02:38 PM
  2. scheduling appointments
    By mikejames in forum Programming
    Replies: 1
    Last Post: 09-30-2011, 04:45 PM
  3. Scheduling Problem
    By Marie in forum Queries
    Replies: 2
    Last Post: 01-19-2011, 09:10 PM
  4. scheduling db
    By JSHEL in forum Database Design
    Replies: 1
    Last Post: 12-12-2010, 09:28 AM
  5. Scheduling database in Access
    By ACS Newbie in forum Access
    Replies: 4
    Last Post: 01-21-2010, 10:32 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums