TalkPHP - Database Structure for a Poll

TalkPHP (http://www.talkphp.com/forums.php)

- General (http://www.talkphp.com/general/)

- - Database Structure for a Poll (http://www.talkphp.com/general/1477-database-structure-poll.html)

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

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.

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

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.

yeah that's a good point, guess i was just following the naming conventions.
2 points to eric.

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.