SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Wizard
    Join Date
    Apr 2004
    Location
    dublin
    Posts
    2,036
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Designing Relational Database

    I want to create a webpage where students sign up to register for a course.

    The student fills out the following form details:
    name, id, and selects a course from a select menu.

    How should I design a relational database to best suit this.

    At the moment I have these tables:

    student {name, id} id is the primary key
    course {course_ID, course} course_ID is primary key
    details {course, id} - links student table and course table

    Is there a simpler way of doing this?

    Thanks,

    Kevin.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by obrienkev
    Is there a simpler way of doing this?
    nope, you have the basic structure right there
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Nearly correct, details should be {course_ID, id}
    (just a typo I assume)

  4. #4
    SitePoint Wizard Defender1's Avatar
    Join Date
    Apr 2001
    Location
    My Computer
    Posts
    2,808
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    A little recommendation.
    Don't use "id".
    Use something more descriptive. If you get in the habbit of using something that generic you'll get very confused down the line when you're joining your tables.
    Defender's Designs
    I'm Getting Married!

    Not-so-patiently awaiting Harry Potter Book 7 *sigh*

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    i disagree

    use "id" for the primary key

    (but if it's not an autonumber or sequence, use something else)

    use "entity_id" for a foreign key to "id" in the "entity" table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard
    Join Date
    Apr 2004
    Location
    dublin
    Posts
    2,036
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No I have details as course and id.

    Reason for this is that the student selects a course so I don't really know how to link the course id to their selection.

    Any ideas?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    could you repeat the question?

    in your details table, you have the course column, which links each details row back to its corresponding course_id in the course table, and you have the id column, which links each details row back to its corresponding id in the student table

    this is correct, so what was the question?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard
    Join Date
    Apr 2004
    Location
    dublin
    Posts
    2,036
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In the details table the course column links to course column incourse table - not to the course_ID column in course table.

    So will this be ok or should I link course_ID? If so how do I do this(as above post)

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    i would definitely link using course_id, not course
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Wizard
    Join Date
    Apr 2004
    Location
    dublin
    Posts
    2,036
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How do I link to course_id?

    The user enters name, number and course. How do I know what course_id they selected??

    Thanks,

    Kevin.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by obrienkev
    The user enters name, number and course. How do I know what course_id they selected??
    how do they select the course?

    do they type in whatever course name they want, and you let them take that course?

    or do they select the course from a dropdown which lists only the available courses?

    that was a hint
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Wizard
    Join Date
    Apr 2004
    Location
    dublin
    Posts
    2,036
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    They select the course via a select menu that displays all the courses in the course database table.

    So how can I link their selection to the course_id field?

    thanks,

    Kevin.

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    if the dropdown displays data from the course table, that's where you use the course_id

    <select>
    <option value="1">description for course 1</option>
    <option value="2">description for course 2</option>
    <option value="3">description for course 3</option>
    </select>
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Wizard
    Join Date
    Apr 2004
    Location
    dublin
    Posts
    2,036
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here is my dropdown menu now:

    PHP Code:
     <select name="course">
     <option value="none">Select Course</option>
     <?php
     
    while ($row mysql_fetch_array($result))
     {
     echo 
    "<option value='" $row["course_ID"] . "'>" $row["course"] . "</option>";
     }
     
    ?>
     </select>
    Then here's the code to process the users selection:

    PHP Code:
     // Check info not already in Details table
     
    if (isset($course) && ($course != "none"))
     {
     
    $find_details mysql_query("select course, ffnumber from details where course = '$course' and ffnumber='$ff' LIMIT 1");
     if (
    mysql_num_rows($find_details) > 0)
     {
         
    $details_id mysql_result($find_details0);
             echo 
    '<p>Student already signed up!</p>';
     }
     else
     {
         
    mysql_query("insert into details values('".$course."', '".$ff."')");
         
    $details_id mysql_insert_id();
             echo 
    mysql_affected_rows().' Student Signed Up.<br />';
     }
     } 
    // end first Details if clause 
    How do I enter the correct course into database? What should I change? Is it just course to course_ID?

    thanks,

    Kevin.

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    kev, i'm sorry, i think you are at the point where there have been enough clues in this thread that you should be able to do this yourself

    it almost feels like i'm doing your homework assignment for you, and i won't do that

    besides, i don't do php

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Wizard
    Join Date
    Apr 2004
    Location
    dublin
    Posts
    2,036
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Rudy!

    Figured it out. Just needed to take a break from it. :-)

    Kevin.


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
  •