Jump to content
Larry Ullman's Book Forums

Recommended Posts

Hi i have a slight problem, i understand how to create and index tables in mysql, but i have this table creation here below... yes the user_id is primary and the username and email are unique, but i do not understand the last line the, INDEX login (username, pass), how will this be used? is login even a column in this database table once this key has been assigned, will taking this off give me a less secure login? what does is actually do?

 

Kind regards

 

CREATE TABLE users (

user_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,

lang_id TINYINT UNSIGNED NOT NULL,

time_zone VARCHAR(30) NOT NULL,

username VARCHAR(30) NOT NULL,

pass CHAR(40) NOT NULL,

email VARCHAR(60) NOT NULL,

PRIMARY KEY (user_id),

UNIQUE (username),

UNIQUE (email),

INDEX login (username, pass)

);

Link to comment
Share on other sites

Also regarding this post in the Yii Book in your Yii Book Larry you put

 

UNIQUE INDEX username_UNIQUE (username ASC),

UNIQUE INDEX email_UNIQUE (email ASC)

 

in the username table so why not

 

INDEX login (username, pass)

 

Should this be added as well or as those two index's already enough for putting an index on username and email when someone is logging in?

 

Also why use:

 

date_entered TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

 

and not just DATE? Do we need the time in seconds exactly looking back, what is the reason for this? Sorry for all the questions. (Ah, because you can use it to minus this timestamp from other so see total time it was there)

Link to comment
Share on other sites

You can add that login index, if you want. I didn't in part because logins are done differently in Yii than you would in a non-framework site. Second, I use TIMESTAMP for markers like date_entered and date_modified in order to most precisely pinpoint them. You'll have multiple records per day, and it'd often be nice to know which came first.

  • Upvote 1
Link to comment
Share on other sites

Okay thanks for that, I have switched over from datetime to timestamp because they are more useful with php functions. I also read online that timestamps 32bit that finish in the year 2037 would most probably be extended to 64bit to buy them more time. This gives me a little more confidence in using them.

Link to comment
Share on other sites

 Share

×
×
  • Create New...