I am writing what's going to be an intranet application, and something of their features is roughly similar to content voting - like what SO, Amazon . com, and several other sites do.
Presuming each votable bit of content includes a unique ID, and every user (they are authenticated) includes a unique ID, the simplest way would appear to become to possess a "votes" table...
ContentID int UserID int VoteValue int
But this produces one row per election - with countless bits of content and hundreds of 1000's of customers, that table's destined to be huge huge huge. Is the easiest method to get it done? I am talking about, if the int takes 4 bytes, each row takes 12 bytes. If your million bits of content obtain a hundred votes, that's 400MB+ kept in storage, yeah? Appears... just like a lot :). Even when the VoteValue is really a tinyint (that is most likely fine) and just 1 byte, that's still around 200 mb within the table. I am talking about say no to.
It is possible to wiser way? Must I store this "votes" table inside a separate database (disregarding potential data integrity issues) to partition it in the "primary" data when it comes to storage and gratifaction?
(I actually do understand that in present day world 400MB ain't a lot - however it appears just like a LOT simply to store votes, yeah?)
Well, yes but you have to consider the problem. Having a million bits of CONTENT:
(Size Content) >> (Size Votes) : where ">>" means "much greater."
For those who have millions of bits of content then that could be a terabyte of information while the votes are 400MB. Large deal right?
I'd include, if you're concerned about scalability, read this blog:
Personally as lengthy as you've good indexes in position, you're going about this the proper way. Based on your usage, for performance you could try to prevent striking the votes table by storing secondary count information, but overall should you must track That has chosen something, you must do it in the manner you've listed.
I would not bother moving to a different database, if you're REALLY concerned in SQL Server you can produce a separate filegroup to carry it.....but not likely necessary.
If you want to track whether a person has chosen for the item, and when you will find different values of election (so 1 star to five stars, for instance), than the is all about as compact because it will get.
Remember that for sensible access speeds, you will need to index the information (two indexes, most likely Body with ContentID because the leading column, one with userID because the leading column).
You will need to decide whether there's grounds to not keep table individually using their company tables. What this signifies is dependent around the DBMS you utilize - with Informix, the table could be within the same database but saved inside a different dbspace, and you will possess the indexes saved in 2 other different dbspaces.
You'll most likely likewise want the ID from the author from the content within the table, for simpler recognition of voting abuse. (Yes, this really is most probably redundant information. An alternate is regularly creating a summary table to determine who's voting on whom.)
For which it's worth, the perlmonks election table appears like this:
`vote_id` int(11) NOT NULL default '0', `voter_user` int(11) NOT NULL default '0', `voted_user` int(11) default NULL, `weight` int(11) NOT NULL default '0', `votetime` datetime NOT NULL default '0000-00-00 00:00:00', `ip` varchar(16) default NULL, PRIMARY KEY (`vote_id`,`voter_user`), KEY `voter_user_idx` (`voter_user`,`votetime`), KEY `voted_user_idx` (`voted_user`,`votetime`)
(election_id may be the content id, ip is definitely an Ip.)