Non-unique many-to-many table design

Question

I'm implementing a voting system for a php project which uses mysql. The important part is that I have to store every voting action separately for statistic reasons. The users can vote for many items multiple times, and every vote has a value (think of it like a donation kinda stuff). So far I have a table votes in which I'm planning to store the votes with the following columns:
user_id - ID of the voting user, foreign key from users table
item_id - ID of the item which the user voted for, foreign key from items table
count - # of votes spent
created - date and time of voting

I'll need to get things out of the table like: Top X voters for an item, all the items that a user have voted for.

My questions are:

1. Is this table design suitable for the task? If it is, how should I index it? If not, where did I go wrong?
2. Would it be more rewarding to create another table beside this one, which has unique rows for the user-item relationship (not storing every vote separately, but update the count row)?

Answer 1

Each base table holds the rows that make a true statement from some fill-in-the-(named-)blanks statement aka predicate.

-- user [userid] has name ...
-- User(user_id, ...)
SELECT * FROM User
-- user [user_id] voted for item [item_id] spending [count] votes on [created]
-- Votes(user_id, item_id, count, created)
SELECT * FROM Votes

(Notice how the shorthand for the predicate is like an SQL declaration for its table. Notice how in the SQL query a base table predicate becomes the table's name.)

Top X voters for an item, all the items that a user have voted for.

Is this table design suitable for the task?

That query can be asked using that design. But only you can know what queries "like" that one are. You have to define sufficient tables/predicates to describe everything you care about in every situation. If Votes records the history of all relevant info about all events then it must be suitable. The query "all the items that user User has voted for" returns rows satisfying predicate

-- user User voted for item [item] spending some count on some date.
-- for some count & created,
    user User voted for item [item_id] spending [count] votes on [created]
-- for some count & created, Votes(User, item_id, count, created)
-- for some user_id, count & created,
     Votes(user_id, item_id, count, created) AND user_id = User
SELECT item_id FROM Votes WHERE user_id = User

(Notice how in the SQL the condition turns up in the WHERE and the columns you keep are the ones that you care about. More here and here on querying.)

If it is, how should I index it?

MySQL automatically indexes primary keys. Generally, index column sets that you JOIN ON, otherwise test, GROUP BY or ORDER BY. MySQL 5.7 Reference Manual 8.3 Optimization and Indexes

Would it be more rewarding to create another table beside this one, which has unique rows for the user-item relationship

If you mean a user-item table for some count & created, [user_id] voted for [item_id] spending [count] votes on [created] and you still want all the individual votings then you still need Votes, and that user-item table is just SELECT user_id, item_id FROM Votes. But if you want to ask about people who haven't voted, you need more.

(not storing every vote separately, but update the count row)

If you don't care about individual votings then you can have a table with user, item and the sum of count for user-item groups. But if you want Votes then that user-item-sum table is expressible in terms of Votes using GROUP BY user_id, item_id & SUM(count).