Login database design questions


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.


having the authenticstion and user data in two different tables does nothing as privelege escalation. you decide what to send to the browser which is the only things the user can see

you can store whatever you want in the session. the session data never leaves the server (unless you choose to echo it/display it somehow)

sounds like premature optimization. which in general is a waste of time as you van spend ages optimizing uour app but without getting it out there and battle proven you can not really know (and dont really have to care) where the bottlenecks are


Yes. I notice this now. I will just stick with fundamentals of programming and keep myself grounded.

Thank you, Sir Jim.