I am hoping for some advice about my methods. I do not understand why usernames and passwords should be in the same table as other, less significant, data. I think along the lines of elevation of privilege. Thus, if a username and password match, then we have a valid member. So we can proceed to collect site settings and personalization from a separate table. I have yet to see a login process coded in such a manner. My current design is as follows (a very simple test procedure missing critical concepts, such as timestamp):
database user_accounts
table users (id int(11) unsigned auto_increment primary key, username varchar(255) not null unique key, password varchar(255) not null);
table user_info ((id int(11) unsigned auto_increment primary key, screenname varchar(255) not null unique key, bgphoto varchar(255) default ('mainbg'));so instead of displaying a username to the world, i set a screen name. Instead of using a username in the session variable, i use the id field. Is it safe to use the id field here? i have no problem logging in and all of the data is present. I change my background photos and everything is working.
my main concern is that i do not know if my database design is usable in the real world. Is it posible that it locks upon heavy usage because i am using two tables? my understanding is that fewer columns means the query works faster. I don’t see a reason to use a join for something as simple as a screen name and a background photo. Am i wrong for thinking this way?
edit: also, i read that a primary key cannot be used with two indexes. I need the screen name to also be unique, hence, the use of a second table.
Thanks you.