Reply To: User voting system problem

[Josh Johnson]

@traq The votes column is mainly for testing whether a user has voted. If a user tries to delete a vote and the field is set to 1, the entry will be removed. I suppose I could do that without it though!

For users table then:
CREATE TABLE users (
user_id int(11) NOT NULL AUTO_INCREMENT,
firstname varchar(100) COLLATE utf8_unicode_ci NOT NULL,
lastname varchar(100) COLLATE utf8_unicode_ci NOT NULL,
email varchar(100) COLLATE utf8_unicode_ci NOT NULL,
email_code varchar(100) COLLATE utf8_unicode_ci NOT NULL,
time_joined int(11) NOT NULL,
location varchar(100) COLLATE utf8_unicode_ci NOT NULL,
password varchar(100) COLLATE utf8_unicode_ci NOT NULL,
portfolio varchar(100) COLLATE utf8_unicode_ci NOT NULL,
experience varchar(100) COLLATE utf8_unicode_ci NOT NULL,
bio varchar(1000) COLLATE utf8_unicode_ci NOT NULL,
confirmed int(11) NOT NULL,
generated_string varchar(35) COLLATE utf8_unicode_ci NOT NULL,
ip varchar(32) COLLATE utf8_unicode_ci NOT NULL,
user_type enum(‘designer’,’developer’,’employer’) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (user_id)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_c

user_votes table
CREATE TABLE user_votes (
vote_id int(11) NOT NULL AUTO_INCREMENT,
user_id int(11) NOT NULL,
voted_by_id int(11) NOT NULL,
votes tinyint(1) NOT NULL,
PRIMARY KEY (vote_id),
UNIQUE KEY vote_id (vote_id),
KEY user_id (user_id),
KEY voted_for_id (voted_by_id),
KEY voted_by_id (voted_by_id),
CONSTRAINT user_votes_ibfk_1 FOREIGN KEY (user_id) REFERENCES users (user_id),
CONSTRAINT user_votes_ibfk_2 FOREIGN KEY (voted_by_id) REFERENCES users (user_id)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

freelancers table
CREATE TABLE freelancers (
freelancer_id int(11) NOT NULL AUTO_INCREMENT,
user_id int(11) NOT NULL,
jobtitle varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
priceperhour int(11) NOT NULL,
PRIMARY KEY (freelancer_id),
KEY user_id (user_id),
CONSTRAINT freelancers_ibfk_1 FOREIGN KEY (user_id) REFERENCES users (user_id)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci