Page 1 of 1

[SOLVED] table structure - noob

Posted: Thu Aug 25, 2005 10:25 am
by imstupid
hey everyone-
keep in mind I have an extremely little grasp on php/MySQL. Here's my question. I've got a table with 6 or 7 columns in that holds member data (first name, last name, location, email, age...) and one of those columns is the person's username. When a person register's, I tell php to check the table for a username first to make sure no other member has that username. Now, because I don't reallly know what I am doing, I didn't set up an ID field or create any keys, etc. and I am just learning that might have been the right thing to do. So, can you give me any advice as to if my current method will have problems in the future (such as ineffecient queries) and if so, do you think it would be worth the time now, to try and us PHPAdmin to create an ID field, set some kind of key (after I read more) and set it to auto_incremement since there is only 30-50 records now. I'm just trying to make sure I'm not going to run into any problems down the road when if there is 5000 members or something. Any help/advice you could give me would be great.

Thanks for everything.

Posted: Thu Aug 25, 2005 10:35 am
by feyd
a primary key can be used to link various records together, however, if the DBMS supports foreign keys you can always use those to keep things in synch. Since you likely aren't working with foreign keys, it's probably a good idea to have them. Besides, it's easier to reference a user by their id than with a name (programmatically)

Posted: Thu Aug 25, 2005 1:32 pm
by imstupid
thanks feyd. I guess i'll take the time to fix it up for the long run. all the other stuff was correct though? meaning I need to create the field like:

create table awesomeness (
ID integer auto_increment,
other fields,
primary key (ID)
);

sub question: would I have to physically assign values to the ID for the existing rows after I create the extra column?

thanks for the patience... I knew I should have learned more about keys first before trying anything.

Posted: Thu Aug 25, 2005 1:35 pm
by feyd
  1. yeah, that's the basic schema of a table...
  2. you'll find out the answer real quick once you do it :P

Posted: Thu Aug 25, 2005 3:00 pm
by imstupid
nice! yeah, that went pretty smooth. thanks again and be sure to keep us updated on the 114-octane fueled supercar