TalkPHP
 
 
Account Login
Latest Articles
» The basic usage of PHPTAL, a XML/XHTML template library for PHP
» Vulnerable methods and the areas they are commonly trusted in.
» Simple way to protect a form from bot
» The Basics On: How Session Stealing Works
» How to keep your forms from double posting data
IRC Channel
IRC Speech Bubble Join the friendly bunch on IRC...
(#TalkPHP on Freenode)

...Also available via a web interface.

See this thread for information on the TalkPHP Free Hugs Initiative™. Subject to availability.
Associates
Associates
CSS Tutorials
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
Old 11-15-2007, 08:24 PM   #1 (permalink)
The Acquainted
Upcoming Programmer 
 
CMellor's Avatar
 
Join Date: Sep 2007
Location: Leeds, UK
Posts: 141
Thanks: 6
CMellor is on a distinguished road
Default Database Structure for a Poll

Hey,

I want to make a poll script to use on my website, on the front page and the user's profile page (they can make their own), but I can't think how I'd set out the database structure for one.

I thought maybe having three tables:
  • poll_question
  • poll_options
  • poll_votes
... then join them all together with the ID's, or something like that. I've checked out other scripts' way of doing a structure, but it seems real sloppy to me some of the coding people do.

Any help is appreciated, if you've wrote a poll script before, maybe you could share how you did you structure in the database.

Thanks,

- Chris
__________________
Not quite a n00b...
CMellor is offline  
Reply With Quote
Old 11-16-2007, 02:26 AM   #2 (permalink)
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
Old 11-16-2007, 06:02 PM   #3 (permalink)
The Acquainted
Upcoming Programmer 
 
CMellor's Avatar
 
Join Date: Sep 2007
Location: Leeds, UK
Posts: 141
Thanks: 6
CMellor is on a distinguished road
Default

Hey,

Thanks for the idea.

Look forward to seeing what else you have to say, if that's what you meant by "following up with a little more"... right now I need to go get my drink on! lol
__________________
Not quite a n00b...
CMellor is offline  
Reply With Quote
Old 11-16-2007, 06:15 PM   #4 (permalink)
The Acquainted
 
wGEric's Avatar
 
Join Date: Nov 2007
Posts: 166
Thanks: 0
wGEric is on a distinguished road
Default

If each poll is only going to have one question then you don't need the poll_questions table. The question can go in the main poll table.
__________________
Eric
wGEric is offline  
Reply With Quote
Old 11-16-2007, 07:22 PM   #5 (permalink)
The Contributor
 
dschreck's Avatar
 
Join Date: Nov 2007
Location: California
Posts: 82
Thanks: 0
dschreck is on a distinguished road
Default

yeah that's a good point, guess i was just following the naming conventions.
2 points to eric.
dschreck is offline  
Reply With Quote
Old 11-17-2007, 01:27 AM   #6 (permalink)
The Contributor
 
dschreck's Avatar
 
Join Date: Nov 2007
Location: California
Posts: 82
Thanks: 0
dschreck is on a distinguished road
Default

oh right, anyways. My point is that is a poll really something that requires a database design ? Unless your site is called 'Polls Unlimited - 1000s of polls for you' i dont think there's going to be any need to invest a lot of time into it.

Polls are generally used to gather a general opinion of a sites registered users. But say, your site is a revolving door of polls for people to vote on. Then I'd invest a little bit more time on breaking up the polls into categories and such.
dschreck is offline  
Reply With Quote
Reply



Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


All times are GMT. The time now is 03:38 PM.

 
     

Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.
Search Engine Optimization by vBSEO 3.1.0
Inactive Reminders By Icora Web Design