I have created a table votes with columns storeid, itemid, userid, and vote which is an INT ranging from 1 to 5.
I want to have three views of this table:
One, store_ratings, with a column storeid and 5 columns onestar, ... fivestars, counting the number of 1s for each store on column onestar, 2s for twostars... and so on.
Another one just the same, but with product_ratings and itemid instead.
A third one, same as the other two, but with user_ratings and userid instead.
As long as I end up with these three views and I do just one insert operation for each vote I can use any schema.
The schema I am using works, but I am worried about its performance. Is there a better way to do this? This is how I am implementing it right now (check out this fiddle to see how it works with some test data):
CREATE TABLE `votes` (
`storeid` INT NOT NULL,
`itemid` INT NOT NULL,
`userid` INT NOT NULL,
`vote` INT NOT NULL,
PRIMARY KEY (`storeid`, `itemid`, `userid`),
KEY `storeid` (`storeid`),
KEY `itemid` (`itemid`),
KEY `userid` (`userid`),
KEY `vote` (`vote`)
);
CREATE OR REPLACE VIEW votes_onestar AS
SELECT itemid, storeid, userid, COUNT(*) AS onestar FROM votes WHERE vote = 1
GROUP BY itemid, storeid, userid;
CREATE OR REPLACE VIEW votes_twostars AS
SELECT itemid, storeid, userid, COUNT(*) AS twostars FROM votes WHERE vote = 2
GROUP BY itemid, storeid, userid;
CREATE OR REPLACE VIEW votes_threestars AS
SELECT itemid, storeid, userid, COUNT(*) AS threestars FROM votes WHERE vote = 3
GROUP BY itemid, storeid, userid;
CREATE OR REPLACE VIEW votes_fourstars AS
SELECT itemid, storeid, userid, COUNT(*) AS fourstars FROM votes WHERE vote = 4
GROUP BY itemid, storeid, userid;
CREATE OR REPLACE VIEW votes_fivestars AS
SELECT itemid, storeid, userid, COUNT(*) AS fivestars FROM votes WHERE vote = 5
GROUP BY itemid, storeid, userid;
CREATE OR REPLACE VIEW votes_matrix AS
SELECT
i1.itemid, i1.storeid, i1.userid,
IFNULL (s1.onestar, 0) onestar,
IFNULL (s2.twostars, 0) twostars,
IFNULL (s3.threestars, 0) threestars,
IFNULL (s4.fourstars, 0) fourstars,
IFNULL (s5.fivestars, 0) fivestars
FROM votes i1
LEFT JOIN votes_onestar s1
ON i1.itemid = s1.itemid AND i1.storeid = s1.storeid AND i1.userid = s1.userid
LEFT JOIN votes_twostars s2
ON i1.itemid = s2.itemid AND i1.storeid = s2.storeid AND i1.userid = s2.userid
LEFT JOIN votes_threestars s3
ON i1.itemid = s3.itemid AND i1.storeid = s3.storeid AND i1.userid = s3.userid
LEFT JOIN votes_fourstars s4
ON i1.itemid = s4.itemid AND i1.storeid = s4.storeid AND i1.userid = s4.userid
LEFT JOIN votes_fivestars s5
ON i1.itemid = s5.itemid AND i1.storeid = s5.storeid AND i1.userid = s5.userid;
CREATE OR REPLACE VIEW store_ratings AS
SELECT
storeid,
SUM(onestar) AS onestar,
SUM(twostars) AS twostars,
SUM(threestars) AS threestars,
SUM(fourstars) AS fourstars,
SUM(fivestars) AS fivestars
FROM votes_matrix
GROUP BY storeid;
CREATE OR REPLACE VIEW user_ratings AS
SELECT
userid,
SUM(onestar) AS onestar,
SUM(twostars) AS twostars,
SUM(threestars) AS threestars,
SUM(fourstars) AS fourstars,
SUM(fivestars) AS fivestars
FROM votes_matrix
GROUP BY userid;
CREATE OR REPLACE VIEW product_ratings AS
SELECT
itemid,
SUM(onestar) AS onestar,
SUM(twostars) AS twostars,
SUM(threestars) AS threestars,
SUM(fourstars) AS fourstars,
SUM(fivestars) AS fivestars
FROM votes_matrix
GROUP BY itemid;
The obvious alternative is to store the votes_matrix view as a table and build the votes table as a view from it instead of the opposite (like this). But having every vote take 5 columns where 1 is enough seems like a waste of space and not very practical to update/insert. Plus it also seems less flexible: what if I decide to switch to 0-5 instead of 1-5; or, even worse, 0-100? It seems easier to adapt the 5 views on the first model than the table on the second one. Anyway, I'd appreciate any thoughts or experiences from anybody who has faced this problem before...
Ended up with a much simpler version of the first model, avoiding all the nested views and subqueries, using IF (the equivalent to Oracle's DECODE, although a more standard CASE would also be fine). I think this will do:
CREATE TABLE `votes` (
`storeid` INT NOT NULL,
`itemid` INT NOT NULL,
`userid` INT NOT NULL,
`vote` INT NOT NULL,
PRIMARY KEY (`storeid`, `itemid`, `userid`),
KEY `storeid` (`storeid`),
KEY `itemid` (`itemid`),
KEY `userid` (`userid`),
KEY `vote` (`vote`)
);
CREATE OR REPLACE VIEW store_ratings AS
SELECT
storeid,
SUM(IF (vote=1,1,0)) AS onestar,
SUM(IF (vote=2,1,0)) AS twostars,
SUM(IF (vote=3,1,0)) AS threestars,
SUM(IF (vote=4,1,0)) AS fourstars,
SUM(IF (vote=5,1,0)) AS fivestars
FROM votes
GROUP BY storeid;
CREATE OR REPLACE VIEW user_ratings AS
SELECT
userid,
SUM(IF (vote=1,1,0)) AS onestar,
SUM(IF (vote=2,1,0)) AS twostars,
SUM(IF (vote=3,1,0)) AS threestars,
SUM(IF (vote=4,1,0)) AS fourstars,
SUM(IF (vote=5,1,0)) AS fivestars
FROM votes
GROUP BY userid;
CREATE OR REPLACE VIEW product_ratings AS
SELECT
itemid,
SUM(IF (vote=1,1,0)) AS onestar,
SUM(IF (vote=2,1,0)) AS twostars,
SUM(IF (vote=3,1,0)) AS threestars,
SUM(IF (vote=4,1,0)) AS fourstars,
SUM(IF (vote=5,1,0)) AS fivestars
FROM votes
GROUP BY itemid;