SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Zealot tconley79's Avatar
    Join Date
    Feb 2004
    Location
    Miami
    Posts
    176
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Student-Mentor database

    I am currently developing a database to track student/mentor appointments. The way it's set up is each student can have multiple mentors and each mentor can have multiple students. Also, each student can have only 10 meetings with EACH of their mentors. I'd like to set up a database structure in which I could query things like:

    "When is Susie's next appointment? Who is it with? And how many does she have left?"

    This is the schema I have so far:

    "STUDENT" TABLE
    -ID
    -Name

    "MENTOR" TABLE
    -ID
    -NAme

    "STUDENT_MENTOR" TABLE
    -ID
    -S_ID
    -M_ID

    "APPOINTMENT" TABLE
    -App_ID
    -Student_Mentor_Pair(ID from "STUDENT MENTOR" TABLE)
    -Time


    ...Where I'm stumped is implementing the appointment tracking.

    Thanks for any Help.

  2. #2
    chown linux:users\ /world Hartmann's Avatar
    Join Date
    Aug 2000
    Location
    Houston, TX, USA
    Posts
    6,455
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    Your schema is actually very good. You could have a "Completed" field in the Appointment table to hold whether the appointment took place.

    You don't need the single ID in the Student Mentor table really. It will just make each S_ID and M_ID combination unique.

    Also, you could hold a "Length" field in the Appointment table to see how long the appointment lasted.

    A simple COUNT() query on the user and mentor id's in the Appointment table will allow you to make sure they don't go over their allotted amount (10).

  3. #3
    SitePoint Zealot tconley79's Avatar
    Join Date
    Feb 2004
    Location
    Miami
    Posts
    176
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Those are great tips, Hartmann, Thanks.
    Using the Count() function makes a lot of sense, too.

    But as far as dropping the "ID" in the Student_mentor table...I need that for the appointment table, no? How else would I store that Susie has an appointment with Mr. Smith at 2pm AND also an appointment with Mrs. Fields at 6pm? Also, for tracking, how would I notice that Susie is on her 9th appointment with Mr. Smith, but only her 2nd with Mrs. Fields?

  4. #4
    chown linux:users\ /world Hartmann's Avatar
    Join Date
    Aug 2000
    Location
    Houston, TX, USA
    Posts
    6,455
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by tconley79
    Those are great tips, Hartmann, Thanks.
    Using the Count() function makes a lot of sense, too.

    But as far as dropping the "ID" in the Student_mentor table...I need that for the appointment table, no? How else would I store that Susie has an appointment with Mr. Smith at 2pm AND also an appointment with Mrs. Fields at 6pm? Also, for tracking, how would I notice that Susie is on her 9th appointment with Mr. Smith, but only her 2nd with Mrs. Fields?
    Well, the M_ID and S_ID together make a unique pair. Mr. Smith is M_ID 1 and Mrs. Fields is M_ID 2. Susie is S_ID 15. So the combination of Mr. Smith and Susie would be 1 and 15, while the combination of Mrs. Fields and Susie is 2 and 15. The numbers together are unique. This is called a composite primary key. You are using two foreign keys to create a single primary key. Does that make sense?

    There are a lot of tutorials out there on database normalization and foreign and primary keys. Do a Google search. I assume you have some database design experience?

  5. #5
    SitePoint Zealot tconley79's Avatar
    Join Date
    Feb 2004
    Location
    Miami
    Posts
    176
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK I'm getting it. I'm familiar with composite keys (but never used them). So by dropping the single ID and using a composite key in the "Student_mentor" table, how would I then rewrite the appointment table?

    And yes I have some minor SQL experience. I've designed and implemented a couple of CMS's for my clients.

    Thanks again.

  6. #6
    SitePoint Zealot tconley79's Avatar
    Join Date
    Feb 2004
    Location
    Miami
    Posts
    176
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In other words...

    Once the STUDENT_MENTOR table looks like this:

    "STUDENT_MENTOR" TABLE
    -S_ID (Primary)
    -M_ID (Primary)

    How do I reference the student and mentor in the APPOINTMENTS table?

    "APPOINTMENT" TABLE
    -App_ID
    ---->Student_Mentor_Pair
    -Time

  7. #7
    chown linux:users\ /world Hartmann's Avatar
    Join Date
    Aug 2000
    Location
    Houston, TX, USA
    Posts
    6,455
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    What database are you using? If you are using MySQL what version?

    In the Appointment table you would just have a S_M_ID that references the two primary keys (as a foreign key). Does that make sense? I think this will accomplish what you are trying to do.

  8. #8
    SitePoint Zealot tconley79's Avatar
    Join Date
    Feb 2004
    Location
    Miami
    Posts
    176
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    yes makes sense...

    I guess I'm looking at it from the query side.
    Suppose I asked the question:
    Who does Student 25 have her session with next Friday?

    I'd start with something like:

    SELECT name FROM mentor, student_mentor, student, appointment
    WHERE mentor.ID = student_mentor.M_ID
    AND student_mentor.S_ID = 25
    AND ....

    Joining the appointment table to the query has me stumped....

    ______

    Yes I'm using MySQL. Not sure which version. This is a web application and my client hasn't chosen a host yet...so I'm not sure.

  9. #9
    chown linux:users\ /world Hartmann's Avatar
    Join Date
    Aug 2000
    Location
    Houston, TX, USA
    Posts
    6,455
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    It would look something like:

    Code:
     SELECT name FROM mentor, student_mentor, student, appointment
     WHERE mentor.ID = student_mentor.M_ID
     AND student_mentor.S_ID = student.ID
     AND appointment.S_ID = student.ID
     AND student.ID = 25
    That would show you all upcoming appointments for the student with ID 25. I see what is confusing you and in that case it may be easier for you to use an ID in the student_mentor table, but, that means that you could have more than one student/mentor record.

  10. #10
    SitePoint Zealot tconley79's Avatar
    Join Date
    Feb 2004
    Location
    Miami
    Posts
    176
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Hartmann
    I see what is confusing you and in that case it may be easier for you to use an ID in the student_mentor table
    Yeh that's what I was thinking..

    Quote Originally Posted by Hartmann
    that means that you could have more than one student/mentor record.
    Good point. I guess I would have to implement some validation.

    THANKS A BUNCH FOR THE HELP.

  11. #11
    chown linux:users\ /world Hartmann's Avatar
    Join Date
    Aug 2000
    Location
    Houston, TX, USA
    Posts
    6,455
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    No problem, wish I could have been a little bit more help.

    I think that you're best bet would be to do a little Googling about database normalization. It's less about queries (as those come later) and more about the theory conceptual stuff when it comes to database design.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •