How about my SQL to create the tables? I've added a registration_date column to the users table (the spacing below is just for readability):
Code:
CREATE TABLE users (
user_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
first_name VARCHAR(20) NOT NULL,
last_name VARCHAR(40) NOT NULL,
state CHAR(2) NOT NULL,
email VARCHAR(60) NOT NULL,
pass CHAR(32) NOT NULL,
registration_date DATETIME NOT NULL,
PRIMARY KEY(user_id),
UNIQUE (email),
INDEX login (email, pass),
INDEX state (state)
);
CREATE TABLE links (
user_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
linkurl VARCHAR(60) NOT NULL,
PRIMARY KEY(user_id)
);
A couple of notes on the users table:
- state will be a dropdown with 2-letter state abbreviation values (CA, TX, WA), hence a CHAR(2)
- pass is declared CHAR(32) as I'm just going to use md5
- email is to be unique so the same email address can only be reigstered once
- email/pass is indexed for faster login lookups?
- state is indexed for faster search by state lookups?
A couple of notes on the links table:
- user_id declared exactly the same as it's counterpart in the users table
- linkurl declared as NOT NULL, though not entirely sure if this is needed? Obviously if a user adds a link record, they'll be providing some value.
Originally Posted by Dr John
Bookmarks