[eluser]_just_me[/eluser]
Hello,
I'd appreciate any help you can offer, though not sure if is more suited for a SQL forum - I'm currently trying to decide on a schema for a voting app I'm building in CI + MySQL, but I'm completely stuck on how to optimise it. The key elements are to allow only one vote per user per item, and be able to build a chart detailing the top items of the month – based on votes received that month.
So far the initial schema is:
Items_table
item_id
total_points
(lots of other fields unrelated to voting)
voting_table
voting_id
item_id
user_id
vote (1 = up; 0 = down)
month_cast
Year_cast
So I'm wondering if it's going to be a case of selecting all information from voting table where month = currentMonth & year = currentYear, somehow running a count and grouping by item_id; if so, how would I go about doing so? Or would I be better off creating a separate table for monthly charts which is updated with each vote, but then should I be concerned with the requirement to update 3 database tables per vote?
I'm not particularly competent – if it shows! – so would really love any help / guidance someone could provide.
Thanks,
_just_me
