Should I create a table to track instances of "missing" records?

Question

I have a table called student and it has the following fields:

idno(primary), lastname, status, password, syearid(foreign)

My problem is I have to store the unvoted students in each school_year(syearid):

I have a table that stores students who have already voted. How should I track the unvoted students? Should I make another table?. One of my problems is when the student registers in school_year (2015) or syearid (2000) and votes it will create a student vote record, when student again votes for the next school_year it will create another record in the student votes table but my question is how to store an unvoted student if the student can register only once in a life time (primary is my idno so student can no longer register again but the school_year of the student is the same when she/he registered). What should I do?

Answer 1

You shouldn't need to explicitly store records of students that didn't vote for a given year. You can simply join students to the vote table and check for null. Students without vote records for a given year will not have voted. I'm assuming the syearid in the student votes table is the voting year and syearid in the students table is the year the student started school.

SELECT s.* FROM student s
LEFT JOIN student_votes sv ON (s.idno = sv.idno AND sv.syearid = 2015)
WHERE sv.syearid IS NULL 

Also, assuming only students from certain years are eligible to vote

SELECT s.* FROM student s
LEFT JOIN student_votes sv ON (s.idno = sv.idno AND sv.syearid = 2015)
WHERE sv.syearid IS NULL 
AND s.syearid IN (2012, 2013, 2014, 2015)