poll DB
I'm doing a poll and determined this morning that I should use a relational DB. It's pretty basic, but it is my first one so I'd appreciate it if y'all could check out the code before I go down this path. create table fsnep_polls ( p_id NUMBER Primary Key, p_date_added date, p_date_last_used date, p_question VARCHAR2(400), p_answer NUMBER Foreign Key REFERENCES fsnep_pollAnswers(pA_id), p_status INT ) CREATE SEQUENCE unique_poll_Num_s START WITH 1 create table fsnep_pollAnswers ( pA_id NUMBER Primary Key, pA_answer INT ) CREATE SEQUENCE unique_pollAnswers_Num_s START WITH 1 create table fsnep_pollVotes ( pV_id NUMBER Primary Key, pV_date_added date, pV_poll_id NUMBER Foreign Key REFERENCES fsnep_polls(p_id), pV_poll_Answer INT Foreign Key REFERENCES fsnep_pollAnswers(pA_id) ) CREATE SEQUENCE unique_pollVotes_Num_s START WITH 1 Thanks and good morning! -- Daniel Kessler Department of Public and Community Health University of Maryland Suite 2387 Valley Drive College Park, MD 20742-2611 301-405-2545 Phone www.phi.umd.edu [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: poll DB
you may also want to throw in a voted by and record their ip into it so they cant keep voting. On Thu, 9 Sep 2004 07:38:38 -0400, Daniel Kessler <[EMAIL PROTECTED]> wrote: > I'm doing a poll and determined this morning that I should use a > relational DB. It's pretty basic, but it is my first one so I'd > appreciate it if y'all could check out the code before I go down this > path. > > > > create table fsnep_polls ( >p_id NUMBER Primary Key, >p_date_added date, >p_date_last_used date, >p_question VARCHAR2(400), >p_answer NUMBER Foreign Key REFERENCES fsnep_pollAnswers(pA_id), >p_status INT > ) > > CREATE SEQUENCE unique_poll_Num_s START WITH 1 > > > > create table fsnep_pollAnswers ( >pA_id NUMBER Primary Key, >pA_answer INT > ) > > CREATE SEQUENCE unique_pollAnswers_Num_s START WITH 1 > > > > create table fsnep_pollVotes ( >pV_id NUMBER Primary Key, >pV_date_added date, >pV_poll_id NUMBER Foreign Key REFERENCES fsnep_polls(p_id), >pV_poll_Answer INT Foreign Key REFERENCES fsnep_pollAnswers(pA_id) > ) > > CREATE SEQUENCE unique_pollVotes_Num_s START WITH 1 > > Thanks and good morning! > > -- > Daniel Kessler > > Department of Public and Community Health > University of Maryland > Suite 2387 Valley Drive > College Park, MD 20742-2611 > 301-405-2545 Phone > www.phi.umd.edu > > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: poll DB
>you may also want to throw in a voted by and record their ip into it >so they cant keep voting. I thought of doing that, but it would exclude community computers and since many of our audience is low-income, alot of their access is community-oriented. So otherwise, I'm doing the right thing and it's set up okay? As I said, it's my first attempt at relational dbs so all comments are welcome. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: poll DB
I'm also working on a poll systems and I decided to keep the number for each option in the same table as the option and just increment the value for example id_poll (fk) id_poll_option (pk) poll_option poll_option_description poll_option_votes if you are going to put the votes in a seperate table you might want to consider storing the date of the vote its only then that you can realize the value of the haveing the votes in a seperate table. -- Original Message -- From: daniel kessler <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] Date: Thu, 09 Sep 2004 09:12:29 -0400 >>you may also want to throw in a voted by and record their ip into it >>so they cant keep voting. > >I thought of doing that, but it would exclude community computers and since many of our audience is low-income, alot of their access is community-oriented. > >So otherwise, I'm doing the right thing and it's set up okay? As I said, it's my first attempt at relational dbs so all comments are welcome. > > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: poll DB
> I'm also working on a poll systems and I decided to keep the number > for each option in the same table as the option and just increment the > value for example > > id_poll (fk) > id_poll_option (pk) > poll_option > poll_option_description > poll_option_votes > > if you are going to put the votes in a seperate table you might want > to consider storing the date of the vote its only then that you can > realize the value of the haveing the votes in a seperate table. But then wouldn't I be writing an entry in the pollDB for every time that it's answered? So, when I make a poll, I write an entry for every different option/answer available. Then I also write an entry for every answer made? Am I understanding correctly? It doesn't seem like a good use of DB space but I'm new to all this. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: poll DB
Looks good to me, here is a layout from one I did tbl_Polls PollID autonumber PK PollQuestion text DateAdded date/time PollStatus number tbl_PollAnswers AnswerID autonumber PK PolID number SortID number Tbl_PollResults ResultID autonumber PK PollID number AnswerID number DateAdded date/time IPAddress text I can grab a poll whos status is NOT 0 from tbl_Polls, then grab all Answers from PollAnswers where PollID = PollID from my first query ordered by SortID and thats all to display the poll and build the form. The form fields were radio buttons named something like PollOption value=#AnswerID# 1 1 1 9/10/2004 192.168.1.101 2 1 3 9/11/2004 192.168.1.102 3 1 2 9/12/2004 192.168.1.103 4 1 3 9/13/2004 192.168.1.104 ResultsID is of course the ID of the record in the result table PollID is what poll the result belongs to AnswerID is the answer they chose DateAdded is the date/time they took the poll IPAddress is #cgi.remote_addr# just so they dont submit over and over Id have to check but Im pretty sure I got result count before displaying the polls. If they were less than 100, I allowed it to be taken again; else I set the status to 0, closing the poll for good. The percentage results I had worked no matter the number of results, but I liked it ending up a nice round number. Now I can grab the number of results for any poll I can get the date last used from the latest date in tbl_Answers If the field existed, add its value to the database its value is the answered, a hidden field for the pollID and your in business _ From: Daniel Kessler [mailto:[EMAIL PROTECTED] Sent: Thursday, September 09, 2004 7:39 AM To: CF-Talk Subject: poll DB I'm doing a poll and determined this morning that I should use a relational DB. It's pretty basic, but it is my first one so I'd appreciate it if y'all could check out the code before I go down this path. create table fsnep_polls ( p_id NUMBER Primary Key, p_date_added date, p_date_last_used date, p_question VARCHAR2(400), p_answer NUMBER Foreign Key REFERENCES fsnep_pollAnswers(pA_id), p_status INT ) CREATE SEQUENCE unique_poll_Num_s START WITH 1 create table fsnep_pollAnswers ( pA_id NUMBER Primary Key, pA_answer INT ) CREATE SEQUENCE unique_pollAnswers_Num_s START WITH 1 create table fsnep_pollVotes ( pV_id NUMBER Primary Key, pV_date_added date, pV_poll_id NUMBER Foreign Key REFERENCES fsnep_polls(p_id), pV_poll_Answer INT Foreign Key REFERENCES fsnep_pollAnswers(pA_id) ) CREATE SEQUENCE unique_pollVotes_Num_s START WITH 1 Thanks and good morning! -- Daniel Kessler Department of Public and Community Health University of Maryland Suite 2387 Valley Drive College Park, MD 20742-2611 301-405-2545 Phone www.phi.umd.edu _ [HYPERLINK "http://www.houseoffusion.com/lists.cfm/link=t:4"Todays Threads] [HYPERLINK "http://www.houseoffusion.com/lists.cfm/link=i:4:177656"This Message] [HYPERLINK "http://www.houseoffusion.com/lists.cfm/link=s:4"Subscription] [HYPERLINK "http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=678.598.4"Fast Unsubscribe] [HYPERLINK "http://www.houseoffusion.com/signin/"User Settings] [HYPERLINK "https://www.paypal.com/cgi-bin/webscr?amount=&item_name=House+of+Fusion&bus iness=donations%40houseoffusion.com&undefined_quantity=&cmd=_xclick"Donation s and Support] _ HYPERLINK "http://www.houseoffusion.com/banners/view.cfm?bannerid=36" \n --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.754 / Virus Database: 504 - Release Date: 9/6/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.759 / Virus Database: 508 - Release Date: 9/9/2004 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: poll DB
This crap is getting annoying >Your message is a reply to anothers post and is more than 100 lines of text. Unless you are >a major writer, your probably adding a lot of the previous replies. Please trim your posts >when replying. Thank you. Looks good to me, here is a layout from one I did tbl_Polls PollID autonumber PK PollQuestion text DateAdded date/time PollStatus number tbl_PollAnswers AnswerID autonumber PK PolID number SortID number Tbl_PollResults ResultID autonumber PK PollID number AnswerID number DateAdded date/time IPAddress text I can grab a poll whos status is NOT 0 from tbl_Polls, then grab all Answers from PollAnswers where PollID = PollID from my first query ordered by SortID and thats all to display the poll and build the form. The form fields were radio buttons named something like PollOption value=#AnswerID# 1 1 1 9/10/2004 192.168.1.101 2 1 3 9/11/2004 192.168.1.102 3 1 2 9/12/2004 192.168.1.103 4 1 3 9/13/2004 192.168.1.104 ResultsID is of course the ID of the record in the result table PollID is what poll the result belongs to AnswerID is the answer they chose DateAdded is the date/time they took the poll IPAddress is #cgi.remote_addr# just so they dont submit over and over Id have to check but Im pretty sure I got result count before displaying the polls. If they were less than 100, I allowed it to be taken again; else I set the status to 0, closing the poll for good. The percentage results I had worked no matter the number of results, but I liked it ending up a nice round number. Now I can grab the number of results for any poll I can get the date last used from the latest date in tbl_Answers Can easily get percentage results from one table If the field existed, add its value to the database its value is the answered, a hidden field for the pollID and your in business. --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.754 / Virus Database: 504 - Release Date: 9/6/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.759 / Virus Database: 508 - Release Date: 9/9/2004 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: poll DB
>But then wouldn't I be writing an entry in the pollDB for every time that it's answered? >So, when I make a poll, I write an entry for every different option/answer available. Then I also write an entry for every answer made? Am I understanding correctly? >It doesn't seem like a good use of DB space but I'm new to all this. > Your right if you stored responses in a seperate table you would end up with a data set that included every response to every poll. If you had 4 polls and each got 100 votes you would have a table that had 4 hundred entries. The good side of this is you can now store the date of each response and if you want too you could store a persons comments. By doing this way you would be able to capture more information than just the normal poll outcomes. The alternative is to simply increment the "vote count" of each option. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: poll DB
I'm building a polling app into my cms right now. Sorry but I haven't built the Oracle versions of the table installers yet, but I can use the mySQL versions cuz its the simplest sql to post. If this can help, great. If anyone has any comments on my design I'd appreciate them as this is a work in progress. The polling app is designed so an admin can create their own via a web interface. Multiple questions per poll are supported. Multiple chart types can be selected and as such there are cfchart-oriented fields in here that go with the admin app and its instructions. Each table is described below CREATE TABLE poll ( ID int(10) unsigned NOT NULL auto_increment, StartDate varchar(10) default NULL, StopDate varchar(10) default NULL, Title varchar(255) default NULL, ChartWidth int(10) unsigned default NULL, ChartType varchar(10) default NULL, Rotated char(3) default NULL, ChartBarColor varchar(7) default NULL, PRIMARY KEY (ID), INDEX StartDate (StartDate), INDEX StopDate (StopDate), ); poll table is the overall parent table for the system. startdate and stopdate are text for app-specific reasons. You would want to use date fields I'm sure. The idea is a poll will appear on the site at a certain point and stop accepting votes and become a part of the poll archive after a certain date. The poll title is for admin reference only. The remaining fields are used by CFChart when displaying the results. CREATE TABLE poll_items ( ID int(10) unsigned NOT NULL auto_increment, ParentID int(10) unsigned default NULL, Slot int(10) unsigned default NULL, Question varchar(255) default NULL, ItemColor varchar(7) default NULL, PRIMARY KEY (ID), INDEX ParentID (ParentID), INDEX Slot (Slot) ); the poll_items table is where the poll questions go. A separate table allows more than one question per poll. ParentID is the corresponding poll ID value. Slot value determines question display order. ItemColor is used by CFChart. CREATE TABLE poll_items_options ( ID int(10) unsigned NOT NULL auto_increment, ParentID int(10) unsigned default NULL, Slot int(10) unsigned default NULL, Display varchar(255) default NULL, SliceColor varchar(7) default NULL, PRIMARY KEY (ID), INDEX ParentID (ParentID), INDEX Slot (Slot) ); poll_items_options holds the list of answers to a given question for poll form display. The parentID is the corresponding poll_items ID value. Slot value determines the order of their display. Display is the displayed answer. SliceColor is for pie-type cfcharts. CREATE TABLE poll_answers ( ID int(10) unsigned NOT NULL auto_increment, ParentID int(10) unsigned default NULL, PollID int(10) unsigned default NULL, Visitor varchar(64) default NULL, PRIMARY KEY (ID), INDEX ParentID (ParentID), INDEX PollID (PollID) INDEX Visitor (Visitor) ); Poll_answers holds voter responses. The parentID is the ID of the chosen poll_item_options record. PollID is the ID in the poll table. Visitor is client.cfid:clientcftoken, which is what I'm using in conjunction with Poll ID to keep people from voting twice. -- --Matt Robertson-- MSB Designs, Inc. mysecretbase.com [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: poll DB
So now I'm trying to create in Oracle the tables for the poll. But when I try to create the first one, which references another table, I get the error, "missing right parenthesis". The only other relational work that I've done was in mySQL so I'm sure that it's a syntax or keyword Oracle issue. Here's the two tables: create table fsnep_polls ( p_id NUMBER Primary Key, p_date_added date, p_date_last_used date, p_question VARCHAR2(400), p_status NUMBER ) CREATE SEQUENCE unique_poll_Num_s START WITH 1 create table fsnep_pollAnswers ( pA_id NUMBER Primary Key, pA_pollID NUMBER Foreign Key REFERENCES fsnep_polls(p_id), pA_answer NUMBER ) And thanks a bunch for all the previous answers. It helped alot once I was able to sit down and read it all. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
