#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2002
    Posts
    20
    Rep Power
    0

    Database structure for small student registration system


    I am currently doing webpage work for a small management learning institute. I already have the PHP written to interact with my SQL database. Meaning that I can insert the given data into a database. However, when you come to register it will not be automatic. We will receive the contact information (name, address, phone, email, etc... as well as educational background, work experience, how you heard about us, etc...). Then after reviewing it send you information about payment and student number and so forth.

    Currently I store all of the information in a contact table, then have to manually copy the same information into a registered table, and manually create student number and username for the person at that time.

    I would like to know if there is a better way to organize the table structure (I'm sure there is), and if possible a way to create both a student number and a username automatically. I just recently graduated and this is my first major project and wanted to have the structure well laid out in case the enrollment of the insitute ever becomes large.

    Any suggestions are welcome, or if you can point me to any resources that would be great.

    Thanks

    Padriac
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2002
    Posts
    20
    Rep Power
    0
    Anyone? Would there be a better forum for this?
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2002
    Location
    NJ, USA
    Posts
    91
    Rep Power
    12
    Do you retain old contact information?

    Sounds like you could get by with a second table for registered students. The second table might be as simple as a student number and name to link it to the first table.
    - Walt

    Will code for food.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2002
    Location
    Osaka
    Posts
    9
    Rep Power
    0
    I think what you are doing is correct i.e keep the
    contacts table and registered table separate.

    I feel you should be able to move record from Contacts
    table to Registered users table through a PHP script
    rather than manually.

    So you should be able to browse through the records
    in the Contact table, select any one, and then click
    on a Register button that would trigger a php script
    that copies that record from contact table to the
    registered users table. The record should then be
    deleted from the Contacts table.

    Ajay
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2002
    Posts
    20
    Rep Power
    0
    Waltjp - I will most likely be keeping the old contact information in case for some reason there applicant status changes and they need to be re-contacted.

    ajaypatil - I do like your idea, and appreciate your support, however I am not sure I have the skills as of yet to produce what you are suggesting. I was hoping to use PHPMyEdit to view and make changes to the DB (I will not always be the one doing the editing so it would be nice to use). However I think this prog only operates on single tables at a time.

    Perhaps someone knows of a better program, or can suggest an addition that can help with this problem.

    BTW I have renamed my tables to Applicants and Registered, figure it makes more sense this way.
    Last edited by Padriac Filch; July 9th, 2002 at 09:28 AM.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2002
    Location
    Osaka
    Posts
    9
    Rep Power
    0
    Padriac,

    It is not very difficult. And you will enjoy writing the script.
    Maybe you can try something simpler to start with.

    What you can do is write a script that takes the unique key
    of an applicant, say name as input.. and then copy the
    record from Applicants table to Registered table.

    To copy the record, you simply have to first select record
    from Applicants table, read the values of all columns and
    then do the insert accordingly.

    Something like this will work:
    Code:
    $sql = SELECT * FROM APPLICANTS WHERE NAME = $name";
    $result = mysql_query($sql);
    
    while ($row = mysql_fetch_object($result)) {
        $first_name = $row->first_name;
        $last_name = $row->second_name;
        //..... etc for all columns.
    }
    
    mysql_free_result($result);
    
    mysql_query("INSERT INTO REGISTERED_USERS VALUES
                           ('$first_name', '$last_name', ... etc for all columns");
    After that, you can try the next step to show all applicants
    in a webpage, select one of them and click on Register button.
    There are lot of PHP tutorials on web.

    Have fun,
    Ajay
  12. #7
  13. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2002
    Posts
    39
    Rep Power
    12
    Why not just have a "registered" column in a single "students" table that gets "checked" ("1") if the student is registered? No need for multiple tables or complicated PHP scripts to copy everything between tables.
  14. #8
  15. No Profile Picture
    Moderator =(8^(|)
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Feb 2002
    Location
    Sacramento, CA
    Posts
    1,710
    Rep Power
    14
    Ya, it would really be smarter not to duplicate your data. That's the whole reason for a relational database, after all. What happens when a student's contact info changes?

    I say one table for contact info, and one table for student info. Then reference the contact info for each student from the student info table.
  16. #9
  17. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2002
    Posts
    39
    Rep Power
    12
    The info in the "contacts" and "students" ("applicants" and "registered") tables is the same, however--hence the data duplication and my suggestion.
  18. #10
  19. No Profile Picture
    Moderator =(8^(|)
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Feb 2002
    Location
    Sacramento, CA
    Posts
    1,710
    Rep Power
    14
    Exactly
  20. #11
  21. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2002
    Posts
    39
    Rep Power
    12
    Um, then why did you suggest 2 tables?
  22. #12
  23. No Profile Picture
    Moderator =(8^(|)
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Feb 2002
    Location
    Sacramento, CA
    Posts
    1,710
    Rep Power
    14
    Ah, sorry, mis-read your post.

    Padriac talks about a student number, username, and payment info. A second table would be necessary for that info.

    As for duplicating the data in the contact info, that would be rather silly.

IMN logo majestic logo threadwatch logo seochat tools logo