I am in the process of transferring my flat file poll system to MySQL.

I am having problems modelling the database. Since each poll has variable number of responses, my initial thought was having one table for each poll with unique set of columns with unique data types (since some answers will be stored as INT or SMALLINT, while others are stored as ENUM [Yes,No]).

Another approach I thought of was having ONE table that would contain a "maximum" number of columns and use SMALLINT for all the answers, and use 0 - 1 (true/false - yes/no) for ENUM type of answers.

But I am open to other suggestions.

Thanks in advance.
========================================
Buh Bye!

Cheers,
Me

Hi -

Do you need to store each and every response as it's own record?

If not, I would say something like:

table name: Polls_Polls
columns: PollID | PollTitle | TotalResponses

table name: Polls_(PollID)_Questions
columns: QID | Question | FormType | Options

table name: Polls_(PollID)_Results
columns: QID | Answer | Total


Then have your poll script increment the results directly in the database.

So for example, you might have:

Polls_Polls
1 | Male/Female | 544
2 | Pets | 271


Polls_1_Questions
Q1 | Are you male or female? | radio | male,female

Polls_2_Questions
Q1 | How many pets do you have? | select | 1,2-3,4-5,over 5
Q2 | What kind of pet? | select | Cats,Dogs,Hamsters,Pythons


Polls_1_Results
Q1 | Male | 253
Q1 | Female | 291

Polls_2_Results
Q1 | 1 | 184
Q1 | 2-3 | 51
Q1 | 3-4 | 27
Q1 | over 5 | 9
Q2 | Cats | 19
Q2 | Dogs | 224
Q2 | Hamsters | 7
Q2 | Pythons | 21

The "Polls_(PollID)_Questions" tables are just for generating your poll forms and results pages via script...

Hope that helps ya.



BTW, how are currently doing it in your flatfile system?

--
Matt G

Matt,

Thank you very much for your detailed reply. I really appreciate it!



No...I will be using a "tracking" table that will store the following data:



to reduce duplicate responses as much as I can...I may also deploy things like random numbers, but that will be separate from the actual POLL data.

With the structure you've laid out, it looks like I will have to create two tables for each survey question and also I don't see how the other two tables are related (no foreign key) other than the table names. Please correct me if I am interpreting your schema incorrectly.



Well, the "structure" of the flat file system is as follows:

last_poll_info.txt
poll_title=Question
reference=URL related to question
show_results=number of votes before showing results
expired=timestamp

last_poll_options.txt
total=Total number
1=Option1=Number of votes
2=Option2=Number of votes
3=Option3=Number of voites
4=Option4=Number of votes
5=Option5=Number of votes

Then there are two other flat files that contain current data for the current poll.

There is also an IP Address tracking file associated with this flat file system.

I was wondering if the following schema would work:



The trick will be dynamically building the form...I mean if all the polls were "standardized" to include only a five point likert scale, then it wouldn't be a problem...The problem is not with the "form type" since all form types will be radio buttons.

I think that your structure would expediate this without a lot of coding. My concern is that I do not want to create two tables for each poll. I have monthly polls on my site, which means that over time, there would be 24 tables per year and there could be around 240 tables over a 10 year period.

Anyway of consolidating your structure into fewer tables???
========================================
Buh Bye!

Cheers,
Me

Eliot,


It's my pleasure! I've been helped by you and others on more than one occassion, so when I get a chance to lend a hand, I'm happy to do so.


Actually, I came up with that structure kinda off the top of my head, and in fact the Polls_(PollID)_Questions table was a last minute addition, so it really wasn't thought through clearly enough. Afterwards, I took a break to run some errands and had some time to put some more thought to it....

Instead of having the main table (Polls_Polls) and then two child tables for each PollID, you could use just three tables: Polls_Polls, Polls_Questions, and Polls_Results.

Polls_Polls would be the same as I already outlined.
PollID | PollTitle | TotalResponses

Polls_Questions would look like:
QustionID | PollID | Question | FormType | Options
(In this table, QuestionID is the primary key. PollID joins to the Polls_Polls table, and is not unique. You said you didn't need FormType, but hey, you might change your mind!)

Polls_Results
QuestionID | Answer | Total
(i.e. Essentially the same as before. In this table, there is no primary key. QuestionID joins to the Polls_Questions table, and is not unique.)

That's it. A one-to-many relationship between Polls_Polls and Polls_Questions, and another one-to-many relationship between Polls_Questions and Polls_Results. I think this should give you all the flexibility you need in designing each poll with as many questions as you like, and also allow dynamic creation of the html poll form.

So, what do you think?

As far as the IP tracking, I'm assuming that has something to do with preventing people from answering too often? I'll leave that one up to you, but my thought would be something easy like:
IP (unique) | DateLastAnswered

Then just check the IP & if "now" is too close to DateLastAnswered, don't allow the submission.

You know, this would be kind of a neat as a Plugin for LinksSQL...

--
Matt G

Thanks again, Matt.

I think your second example is much better since it properly joins the tables in question.

I'll give it a whirl and see what I can come up with.

BTW: I am using LINKS SQL v1.13 as the core application to run the poll script.

I won't be upgrading to LINKS SQL v.2.X anytime soon...don't really need to...since I have all that I really need with LINKS SQL v.1.13...
========================================
Buh Bye!

Cheers,
Me

Okay...I think I got it figured out (at least from the DB schema - ER perspective...the coding will be another on-going story)...

Poll_Info



Poll_Answers



Poll_Vote_Track



Note: I think that rather than adding a third table for "Answers", the numerical values of answers can be stored in one table. Also, in terms of data processing, it may be faster than joining a third table.

One coding challenge will be sorting "answers" in the end-user poll form...

Example:

Poll1:

Yes
No

Poll2:

Strongly Agree
Agree
Doesn't Matter
Disagree
Strongly Disagree

As you can see, there is no apparent pattern between the two set of answers...I guess I could add another column called Answer_Sort, which would be a SMALLINT column and the values entered would be 1-X (depending on the total number of answers). Then I can do an ORDER BY Answer_Sort.

But I won't be able to explore solutions until I start coding the end-user forms and views.

Let me know if you see any gaps.

Thanks again, Matt.
========================================
Buh Bye!

Cheers,
Me

You can use:

Poll_Reference => ['3', 'TEXT', '25', '255', '0', '', '^https?://'],

instead of

Poll_Reference => ['3', 'TEXT', '25', '255', '0', '', '^http://|https://'],

Thank you.
========================================
Buh Bye!

Cheers,
Me

Hi Eliot,

I don't think your Poll_Answers table is going to work if you are going to possibly have multiple questions in a single Poll...

You need some way to store the values of the variable number of responses to each question in a poll. I'm not exactly clear on what you are trying to do here. Are you wanting to combine the Polls_Questions and Polls_Results table in my previous example?

Now, if ALL of your polls are only ever going to have one question, then you could do it with two tables, but I think you need to make a couple changes...

First, you need to make sure you define the "question" for your poll... (I'm assuming you are doing this via the Poll_Title field, but just wanted to be clear. i.e. In my example, I had a PollTitle field that was meant to be used like a short "label" for admin identification.)

Next, the FormType column would go into your Poll_Info table. This is because Poll_Answers will contain the "options" that will populate the form element. (You'd never have two answers to the same question that were parts of different form elements.) Plus, I think you wanted to default to "Radio"? Also, I'm not understanding how you'd implement a text field for a poll answer. For, how would you count the results? Hmmm...I guess you could add a new record to the Poll_Answers table every time someone entered a response that didn't exist for that poll...

As for sorting, in Poll_Answers you could have the AnswerID auto-increment and then just make sure your answer options are added to the table in the order you want, then just sort on that field... (You could also auto-increment the PollID in Poll_Info).

And one last thing: Looking at your table structures, I'm not absolutely clear on what you are doing with all of your columns, but a couple things you might want to double check:

Poll_Start - should this be some type of date field?
SMALLINT - I don't remember the max # that can be entered in these fields... something to consider for your Poll_Responses and Total columns. (a busy site might exceed the limit maybe?)

Good luck and let me know how it goes!

PS: In your previous post you mentioned using LinksSQL 1.x... does that version not have plugin support? I'm not familiar with it since I got my license at v2.0.3. Maybe I'll try to give something like this a shot one day... after I figure out how to create a plugin! (Haven't tried it yet!)

--
Matt G

Matt,

Thank you for your reply. I really appreciate it.

The reason that I think two tables will work is that the Poll_Info actually contains the "question" or "statement" via the Poll_Title column. So, there is really no need to associate multiple questions with polls...since each poll has one question/statement.

About the data types...I don't foresee more than 100 "hits" per answer since the average total number of responses I receive per month is 120...some more receptive polls have had up to 400 responses. So, the SMALLINT type is appropriate for the "total" and "responses" columns.



Nope...the stable plugin system was available in later versions of 2.X. But I believe that the plugin system is only useful for non-programmers who may not understand the logic of the application and don't know what files to edit...the plugin system expediates adding new features...

But for me, I already have an integrated user management system, I don't have a lot of categories or links in my directory (v.2.X has wonderful tools for large directory of links) since I focus on quality not quantity, and I have enough Perl and CGI knowledge to create "add-ons" quite efficiently and quickly...about three times faster than LINKS 2.0 where more codes had to be programmed.

So, I really don't feel the need to upgrade at this time, and probably not until I get a dedicated server...which won't be until 2003 since I am now saving money for it.
========================================
Buh Bye!

Cheers,
Me

Eliot,


Yes, I agree. Two tables would work fine for a poll system with only one question per poll. On the other hand, are you sure that you'll always want to do it this way? I can envision poll-type situations where you may want to ask multiple questions. For example, consider a "computer purchasing" poll:

q: When was your last computer purchase?
a: (within last month, last 6 mos, 6mos-1yr, 1yr-2yr, over 2yr, never)

q: What price range?
a: (under $500, $500-$1000, $1000-$2000, $2000+)

q: It's primary use?
a: (Business, email/surf the web, games, etc., none of the above)

Anyway, that's just a thought...



There is one important benefit of a plugin system from a developers perspective... it can make upgrading to future versions a whole lot easier. For example, I have several modifications I am working on in the devlopment of my site, and while hacking the LinksSQL scripts might be easier and quicker in the short run, every time I wanted to upgrade I'd have to make all those hacks again and again... But, if I force myself to create modifications through the plugin system wherever possible, then, at least in theory, it should just be a matter of doing the upgrade and going on my merry way! (Plus updating the couple mods that did not have plugin hook support.)

Well, anyway, hope you get this up and running like you want... and let me know how it goes!


--
Matt G

Matt,

Thanks for the continued input. I appreciate it.

I think that I can handle multiple polls by simply adding another "trigger" column in the tbl_Poll_Info and checking for the "archived" column as well. I don't feel the need to add a tbl_Poll_Questions table...seems to redundant and more complicated in terms of data processing and joining more than two tables.

About the plugin system, yes, it does allow for fluid upgrades, but again, I don't see the need for it at this time or in the near future.

See ya.
========================================
Buh Bye!

Cheers,
Me

Thanks again, Matt!

I got it working!

Check out:

http://vlib.anthrotech.com/

AND

http://vlib.anthrotech.com/...l.cgi?showarchives=1

Very cool! Thanks again!

One more flat file system (Postcard Center) to go and I will have upgraded all my flat file systems to MySQL using LINKS SQL v.1.X.

Yippee!

========================================
Buh Bye!

Cheers,
Me

Hey, very nice, Eliot!



(Been away from the forums for a couple weeks, just got a chance to check it out...)

--
Matt G

Thanks again for your advice, Matt.

There are couple things I need to clean up (in terms of making the FIELDTYPE field more flexible in the auto-generated forms)...but for now, since all the surveys only contain radio buttons. it is not a big deal....

I am very pleased...with it being automated, now I can track more accurate stats, since if there is not a survey of the month created, it will show an error message stating that there is no survey to participate in...before, if I forgot to update the survey, I would get results for a couple days into the next month...hehe!

Very cool, though!

Thanks for your assistance!
========================================
Buh Bye!

Cheers,
Me