Schema RFC

[roertbb]

CREATE KEYSPACE IF NOT EXISTS casspoll 
WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor' : 3 };
USE casspoll;

CREATE TABLE IF NOT EXISTS Polls (
    pollId uuid,
    title text,
    description text,
    dueTime timestamp,
    pollType int,

    PRIMARY KEY (pollId)
);

CREATE TABLE IF NOT EXISTS Answers (
    answerId uuid,
    answer text,
    pollId uuid,

    PRIMARY KEY (pollId, answerId)
)

-- idea #1
-- "server" prevents from inserting due to exceeding the deadline
CREATE TABLE IF NOT EXISTS Votes (
    pollId uuid,
    answerId uuid,
    votesNo counter,
    PRIMARY KEY (pollId, answerId)
);

-- voting
-- now := time.Now().Unix()
SELECT dueTime FROM polls WHERE pollId = ?;
-- if now < dueTime {}
--     for answer := range selectedAnswers {
UPDATE Votes SET votesNo = votesNo + 1 WHERE pollId = ? AND answerId = ?
--     }
-- }

-- calculating votes
-- for answer := range answers {
SELECT votesNo FROM Votes WHERE pollId = ? AND answerId = ?
-- }

-- idea #2
CREATE TABLE IF NOT EXISTS Votes (
    pollId uuid,
    answerId uuid,
    voterId uuid,

    PRIMARY KEY (pollId, answerId)
);

-- voting
-- now := time.Now().Unix()
-- for answer := range selectedAnswers {
INSERT INTO Votes (voterId) VALUES (?) WHERE pollId = ? AND answerId = ? USING TIMESTAMP now;
-- }

-- calculating votes
-- for answer := range answers {
-- SELECT COUNT(*) FROM Votes WHERE pollId = ? AND answerId = ? AND writetime(voterId) < dueTime
-- }

SELECT COUNT(*) FROM Votes WHERE pollId = ? AND writetime(voterId) < dueTime GROUP BY answerId

-- other options
-- + Votes include text for answer, no need for Answers table