View Single Post
Old 11-16-2007, 02:26 AM   #2 (permalink)
dschreck
The Contributor
 
dschreck's Avatar
 
Join Date: Nov 2007
Location: California
Posts: 82
Thanks: 0
dschreck is on a distinguished road
Default

I haven't made a poll script in a while, but I agree, even the ones I've done are sloppy, because they're just polls ;)

Here's how I'd lay it out... roughly..

Code:
MySQL:

CREATE TABLE polls (
	id INT NOT NULL AUTO_INCREMENT,
	name VARCHAR(200) NOT NULL,
	description TINYEXT,
	vote_starts DATETIME,
	vote_ends DATETIME,	
	enabled TINYINT(2) DEFAULT 1,
	PRIMARY KEY(id)
);

CREATE TABLE poll_questions (
	id INT NOT NULL AUTO_INCREMENT,
	poll_id INT NOT NULL DEFAULT 0,
	question TINYTEXT NOT NULL,
	PRIMARY KEY(id)
);

CREATE INDEX parent_id ON poll_questions (parent_id);

CREATE TABLE poll_votes (
	id INT NOT NULL AUTO_INCREMENT,	
	option_id INT NOT NULL,
	post_date DATETIME,
	PRIMARY KEY(id)
);

CREATE INDEX option_id ON poll_votes (option_id);

CREATE TABLE poll_options (
	id INT NOT NULL AUTO_INCREMENT,
	question_id INT NOT NULL,
	option_text VARCHAR(160) NOT NULL,
	PRIMARY KEY(id)
);

CREATE INDEX question_id ON poll_options (question_id);
Kinda rushing to leave work at the moment, but that's how i'd kinda set it up. Have main lookup table, called Polls, controls everything. Start and end dates, dscription, name, etc etc. Then have the others broken into smaller FK relations...

Hope that helps, I'll check back in later tonight to follow up with a little more.
dschreck is offline  
Reply With Quote