advice for busy database

[astralaaron]

Say I have something similar to YouTube's thumbs up and down for certain content on a site... is this a good way to go about handling the data?

 

- each post will have fields for the thumbs up / down count.

- there will be a table which keeps track of all thumbs up / down entries as well as the user Id

 

The table to track all of them would be so I can make sure the same user is not pressing thumbs up or down multiple times..

 

I guess what I am worried about is the queries going really slow after that thumbs up/down table gets really big. Any advice? or do I have the right idea?

Edited December 10, 2013 by astralaaron

[justsomeguy]

Tables like that do get big. No matter how you slice it, this is a scaling problem. You need to store data that will basically keep increasing, the only reason you would delete records from that table is if a user or piece of content was deleted. It's always going to be a scaling problem, but you can mitigate it by having multiple smaller tables, sort of like a hash table. You could split it up into 50 tables, for example, and use the remainder of the user ID divided by 50 to determine which table a certain user should use. However you decide to do it though, scaling is going to be an issue, there's not a way around it if you need to store that data like that.

 

Another way to do that would be to have a single field in the user's record, and store a serialized array there with all of the data for that user. You won't be able to search that field in a query though, each time they rate something you would need to get that field from the database, unserialize the array, check it, and save it back. So that's still extra time, but it might be more efficient in the long run. You wouldn't be able to save that data in the session because they could log in from multiple computers first and then rate the same thing once on each computer.

[astralaaron]

thanks for the input JSG. I like the second suggestion that you made.