#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2000
    Posts
    4
    Rep Power
    0
    i am developing a web site using php+mysql to allow users to register/signup...

    i am considering 2 options...

    1) store userid + password + email + personal particulars such as address, contact info ...(about 20 fields) all in one table

    2) use one table to store [userid + password + email] and another to store [userid + all personal particulars]

    the only queries would be insert/update/select

    option 1), though more straight forward, may result in more "congestion" as the table is likely to be used very often...
    while option 2) may result in inconsistent records as mysql does not support transactions and rollbacks

    any clever way of implementing option 1) without incurring high overhead? or is my concern unfounded?

    thanks
  2. #2
  3. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    18
    Option 1 shouldn't be too big of a concern if you only pull the fields you need when querying.

    Option 2 shouldn't be a concern as you can use the last insert id option to get the userid (assuming you are using an auto_increment field to generate it) for the second table update. IOW, the userid should be auto_incremented in only 1 table.

    The logical flow would be:

    1) insert into first table
    2) get last insert id
    3) insert into second table setting userid to the userid received with last_insert_id

  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2000
    Posts
    4
    Rep Power
    0
    > 1) insert into first table
    > 2) get last insert id
    > 3) insert into second table setting userid to the userid received with last_insert_id

    my concern is that if 1) is successful but 3) is not - maybe user hit STOP button after 1) but before 3), or database insertion error, then i'll be in trouble...right?
  6. #4
  7. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    18
    You'd only be in trouble insofar as that one insert. The next user would still be correct. You can set up a cron job to compare the two tables periodically, or have the second table be the one with just the login id. Set ALL info including the login info into the first table. If someone signs in and isn't in the second table you could verify against the first to double check. If they are there, update the 2nd table then.

    BTW, the user hitting the stop button is not going to affect the PHP script in most circumstances, especially if you are not outputting to the browser between the two queries, the script will continue to run.
    There is also a setting I can't recall now, check the manual, that tells PHP to execute the script to completion no matter what the user does.

    As far as errors, thorough testing should catch all possible bugs in your code that might cause a problem with the second insert.

    Still, a once an hour cron job should catch any problems and be more than sufficient. If you really want to see if you had a concern or not, you can have the cron job log any repairs it performs.

Similar Threads

  1. MySQL Table Advice Needed
    By headhoncho in forum MySQL Help
    Replies: 2
    Last Post: September 30th, 2003, 06:23 AM
  2. Sports seasons schedule and players table design
    By Druffus in forum MySQL Help
    Replies: 0
    Last Post: August 30th, 2003, 01:44 PM
  3. VB Excel help needed: building a table via a macro
    By Jimfx in forum Visual Basic Programming
    Replies: 0
    Last Post: August 14th, 2003, 07:37 PM
  4. database design basics - big table searches
    By hopper in forum Beginner Programming
    Replies: 9
    Last Post: March 11th, 2003, 05:40 AM
  5. design table in MySQL
    By itsource in forum MySQL Help
    Replies: 1
    Last Post: April 30th, 2002, 05:35 PM

IMN logo majestic logo threadwatch logo seochat tools logo