SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Thread: MySQL indexing

  1. #1
    SitePoint Member
    Join Date
    Mar 2002
    Location
    Oklahoma City
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL indexing

    In an on-line learning database, I have an enrollments table where each record is uniquely identified by its courseID and studentID. Therefore the primary key consists of those two fields.

    But in the login process, I need to find enrollment records that match the email address and password entered on a login page. (It is possible that more than one record exists with the same email/password.) It would be inefficient to search sequentially through all the enrollments looking for those with a specific email/password. What is an efficient way of quickly finding those records?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    the email and password columns must be in the student table, not the enrollment

    the enrollment table will have, as you described, courseID and studentID

    you said "It is possible that more than one record exists with the same email/password" but i'll bet you were thinking of the enrollment table, where the same student will have multiple enrollments

    by the way, with courseID and studentID together as the primary key, the same student can enroll for the same course only once, so if you need to, add a third column to the primary key for repeat enrollments


    as for your question about looking up the email and password, in your student table, assign a unique key to them

    in the login procedure, retrieve email and password from the student table to authorize the visitor, but then carry the studentID as a session variable and use it on the page which looks up the student's courses by doing a query on the enrollments table using the session studentID as the value:

    Code:
    select coursename
      from enrollments
    inner
      join courses
        on nerollments.courseID = courses.courseID
     where studentID = sessionID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •