I want to add a table to an existing database to enable a ratings script and to track which IP has voted for each item. The only information I need in this table are: the id of the item the ip numbers that voted for this item + maybe a unigue record number but read the rest first. Each item id number will be unique as that is pulled from another table. Each id number could have multiple ip numbers voting for it. I am thinking primary index on the item number and just have all the ip numbers inserted into the same field as they vote for each item. Is there a better way to do this? Looking for speed and ease on the server.
You just need 1 table with 2 fields--ip and item. As long as an ip can only vote for one item, then you should make the ip field the primary key and put an index on the item.
If this means that an IP can vote for multiple items (but only once for each item) then the primary key will need to include both ip and item_id. If your ratings script simply allows an IP to vote any item any number of times then you will need an additional unique record number which then becomes your primary key.
Agreed. Even if you're checking to see if a user has already voted for an item somewhere else (e.g. by username) the simple operation of adding an IP to the column for that item ID is going to be a chore for your server. If you do what the above poster said, and just have a 2 column table where both ID and IP are the primary keys your database will be very quick. And if you aren't taking care of validation elsewhere, you can check for non-unique PKs when inserting data to determine if an IP has already voted for an item. A lot of rows won't slow down any SQL database. Joining a table with others will slow the database will make things much slower. But, unless you're working with a lot of records, you could produce a fully normalized solution with two tables that wouldn't slow anything down.
That is what I want to do. I am writing the script so I can do whatever I want. Right now I have hit a snag with retrieving the IPs as it is causing a mysql syntax error. I think I need to treat them a strings so it will ignore the periods.
You need add two tables! 1st: id|ip indexes for 1st table: id - primary ip - unique 2nd: id|ip_id|item_id indexes for 2nd: id - primary ip_id - index item_id - index This will be fastest way to get data!
What kind of syntax error are you getting from mysql? Also, what data type is the column you're inserting the IPs into, if not a varchar? Fully normalized is usually never the fastest, just the prettiest. Also, I'm not sure where you're getting your columns, but if you did want to normalize this out completely, there would be two tables, like you said, but with some differences. Table: item PK itemID description price etc Code (markup): Table: itemVote PK itemID (foreign key to item table) PK ipAddress Code (markup): but, you'd need to create a third table with just IP addresses in them, and then use the itemVote table to create a many-to-many relationship between the item and ipAddress tables if you wanted it to be a perfect design. But that's not very practical for the implementation, since the joins will be where the slow down occurs.
My offer still is better,faster than yours , because in my table 1 ip will be added 1 time not millions so it will be faster to get data! Go to google and search for mysql tutorials!
Thanks to all who posted. I got this working by deleting all the crappy sample code I found online and and handling the process with 2 line of clean code that I wrote. I went with one table, two fields ip and id, indexed them both and no primary. This site is not going to have million entries so it will be fine. Repped all who tried to help and thanks.