Creating a poll using MySQL | Tables question

[broncozr]

I'm working on a poll that will keep track of the votes in a MySQL database. I'll have three table---a table that records the votes; a table that keeps track of IP addresses (to prevent multiple votes); and a table that keeps track of miscellaneous information such as the poll question, etc.

I'm not well versed in making MySQL tables/DB's efficient, and I was wondering.... Would it be better to have three big tables (one for all the votes, one for all the IP's, one for all the poll questions) or three tables for each poll?


Thanks!

[oracleguy]

Three big tables for sure, you never would want to create a new set of tables for each poll. Plus there is no reason to. I would also add a fourth table for the poll choices.

So your poll table holds the question, your choices table has all the poll choices and the poll ID they go to, the IP table has IP addresses of people that have voted on any poll. Then your votes table would link them all together with the poll ID, choice ID and the IP ID.

[Old Pedant]

Keeping track of IP addresses is a pretty bad idea.

Many more than half the people in the world do *NOT* have a static IP address.

Consider people who use AOL or Earthlink or similars ISPs. They have literally *millions* of customers and yet they use only a handful of IP addresses, in comparison.

So if you block one of those address, you are blocking hundreds or thousands of people.

Worse, the next time that person comes to your site, it's more than likely that he will *not* be using the same IP address!

In addition to ISPs such as this, there are also all those people who work in offices and sit behind proxies. Microsoft, for example, has what...40000 people in the Seattle area? And yet they all go through a handful of IP addresses.

Even here in my house, we have 3 computers sharing a router, and so they all have the same ip address. (And, to top it off, my ISP switches my IP address about once a week or so.)

I'm not sure why people have picked up on IP addresses as a solution to anything. In general, they aren't.

[broncozr]

Thanks for the replies. I know that using IP's has its pitfalls. I was planning on setting a cookie as well. As an alternative, I had considered just keeping track of IP's in case somebody abused the poll. If they did, I suppose that I could block that IP.

Any other suggestions? Is there any way to get the name of the user's computer? After a little bit of googling, I have read that different OS's offer different access (maybe none?) to the name of the user's computer. The only reason I ask this is that I've noticed that the poll that Matt Drudge occasionally uses on his site seems to rely on something other than a cookie or an IP address. I think I've deleted my cookies and navigated to his site from a different IP. I believe that it still "recognized" me.

[Old Pedant]

It's a huge security hole if a website can identify an individual user's computer. Browsers work like crazy to block that kind of stuff. That said, there are tons of viruses and trojans out there that dig holes into your security, so maybe Matt Drudge has managed to install one of those on your machine. Whoever Matt Drudge is.

[s0me0ne]

Sorry for bumping up an old thread, but I found this via Google search and wanted to reply and ask a question.

I know many sites require registration to vote on polls, but if you make one that isnt, then you need something in place, something a little better than sessions and cookies, perhaps to prevent cheating.

So, I think it makes sense to log the IP with the vote, but only if you put a timestamp and therefore check to make sure someone with the same IP hasn't voted on the same poll within the last 10minutes or something.

However, I don't see the benefit of putting the IPs in their own table, because all that would be in there is the id and ip.

But I'm curious how the Votes table should be setup?
Should it look like this? :confused:
VOTES TABLE
----
id
poll_id
choice_id
timestamp
ip

[Old Pedant]

Why do you need to record individual votes????

It's just a poll, so I'd think you would simply have:

Table: polls
pollid : int, pk
question : text

Table: choices
pollid : int, fk to polls
choiceid : int, pk
voteCount : int
choice : text


Then you'd have the separate timed table that you mentioned:

Table: recentIPs
ip : text or number, pk
when : datetime

You could occasionally clean out records from the recentIPs, to keep the table size smaller, but it's not strictly needed.

[s0me0ne]

Thanks for the reply. That makes sense, however if you have multiple polls you might lock somebody out from voting on another poll. So I guess thast where the votes table came into play. But now that I think about it, it probably could look like this

table: votes
id: pk
poll_id: fk
ip_id: fk
timestamp? I'm not sure if it would go in the IP table or in the votes table.

I'm still new to working with MySQL and databases, though.

[Old Pedant]

I wouldn't bother with an IP table unless you really thought you would run many polls at the same time. Two or three at same time? Not worth the hassle of the added table and normalization. I know, heresy.

[broncozr]

similar task .. if I wanted to put together a comments widget for blogs on a site, would I dump all of the comments into one table and pull them out based on the blog's id? Each blog has a unique id (int), and the blogs are currently stored in separate tables, based on each blogger---probably an inefficient design.

I would probably also want to create a username/password table for banning unruly users. I assume that would be a different table ... and probably a different thread topic!

Here is what I had in mind for the comments table:
Table: comments
blog_id: int (make this a foreign key?)
comment_id : int, pk
comment_text : text
user_id: int (make this a foreign key?)



I have to admit I'm a little unsure about dealing with foreign keys. I have no experience dealing with them....

Is mySQL efficient enough performance-wise to deal with sorting through several thousand comments if I place them all in one table? For example, there would be several hundred users commenting on 5-10 blogs per week. It would add up pretty fast.


Thanks for any suggestions.