Is there a good way to store de-identified voting data related to an election, while still ensuring people can't cast multiple votes? What is the best practices for storing this kind of data, particularly over longer periods of time?
I am one of a few web admins and database administrators for a recreational club. We have over 500 members. Each year, we hold an election to vote for new board members, and election data is stored in our database. We have the following database tables that store over a decade of voting data:
We're rewriting parts of website, including election-related content, and so it seemed like a good opportunity to think more critically about this database structure and whether it conforms with best practices. My main concern is storing memberIDs associated with each election vote. A further impetus for putting a spotlight on this practice is that one of the members who is running for the board this year happens to be a web administrator will full access to the database.
At the end of the day, I trust these folks, and I'm not too worried about anything bad happening. But I'm curious what should be considered a best practice for handling this kind of data.
While not directly relevant, we use ASP.NET Membership right now (but soon will be switching to ASP.NET Identity).
I would probably go with a structure like this:
Election and ElectionCandidates stays pretty much the same,
ElectionVotes stores one row per vote per candidate per election,
MemberVotes stores one row per member per election.
When a member votes, you add a row in ElectionVotes for the candidate they voted for, and a row in MemberVotes for the member that voted (a simple stored procedure with a transaction should do that easily).
You should also not store stuff like voting date in neither of these tables, to prevent comparing rows by date.
This way, you don't have anything that ties the member that voted to the candidate they voted for.