Help with database structure

I'm going to make an image-rating system, it will contain very many images, let's say 500.000 and then the members, let's say 50.000 should be able to vote for these images, but one member should only be able to vote once / image. I'm using MySQL.

I've thought of building it with two tables, but it feels like the system will be very slow, and the only thing I've done to speed things up is to add total votes and average votes to the images-table. Here's my structure:

Table: images
- image_id
- total_votes
- average_vote
 
 
Table: votes
- vote_id
- image_id
- user_id
- timestamp

Code (markup):

How would you build this system? It feels like mine would be way to heavy for the server.

 

However if you want to make the restriction that only one time restriction, there would be a large number of entries in mysql

 

What you have looks fine. If allowing each user to vote once is a requirement, then you're not going to be able to get away with having less data.

 

Awesome, looks good!!

 

One other thing I forgot to mention which you may have already considered and is basic. Make sure you have indexes on the columns you're joining. That should help the querying a lot.

 

How do I do this join thing? And I'm not sure about the indexes, in table 1, the "image_id" should be the "primary key", right? And I will add that as auto_increament

How should I do in table 2?
The thing that should be unique there is a combination of image_id and user_id, should I do a new row called "image_id|user_id" and set that as unique or something? So the information in it for image_id 5 and user_id 40 will be "5|40"?

 

Yes, set image_id as your PK in table 1.

If by row you mean index, then yes. In table 2, set your PK as vote_id and have it auto increment.

Then, I'm going to assume you will have a query that looks like this:

select * from images i, users u, votes v
where i.image_id = v.image_id
and v.user_id = u.user_id

In this case, you will want to create another unique index on image_id and user_id.

Depending upon what the other queries you have on your vote tables are, you may want to create non-unique indexes on individual columns.

 

Wouldn't a composite primary key on table 2 of vote_id and image_id be better? That guarantees each user can vote only once, and there's no need for a unique index or a separate field.