CareerCup

attributes{roll_no_of_voter{PK}, candidate_id}

There should be only one primary key in vote tables i.e roll numer (or voter_id) of the voter so that one voter can have only one entry in the votes tables.

If we make both (voter_id & candidate_id ) together as primary key , then there will be multiple votes

voter1 candidate2
voter1 candidate1
so voter_id should be primary key.

Table 1 for candidates: Attributes {candidate_id(primary key), candidate_name, email, contact_no.,roll_no}

Table 2 for voters: attributes{voter_id/roll_no,name, email, contact_no, candidate_id_of_whom_he/she_is_voting, vote_id (auto incerment to count the number of votes)}

Table 3 for votes: attributes{roll_no_of_voter, candidate_id}

I am a newbie. Please correct me if any error is in the above answer.

Tables
Post
postId (PK) PosName

Candidates
candidateId(PK) PosID(FK) Name(Unique)

Voters
voterId Name

VotingRecord
VoterId(PK) CandidateID(FK) OnDate

Table 1 for candidate
Table 2 for voters
Table 3 for votes

Student -> id(PK), name
Candidate -> id(PK, FK from Student), post
Voter -> id(PK, FK from Student), vote(FK from Candidate)

where Voter table should have unique (id + vote)

CREATE TABLE `user` (
  `user_id` int(11) NOT NULL auto_increment,
  `user_name` varchar(64) NOT NULL,
  PRIMARY KEY  (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `post` (
  `post_id` int(11) NOT NULL auto_increment,
  `post_name` varchar(64) NOT NULL,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY  (`post_id`),
  CONSTRAINT `user_post` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `vote` (
  `vote_id` int(11) NOT NULL auto_increment,
  `user_id` int(11) NOT NULL,
  `post_id` int(11) NOT NULL,
  PRIMARY KEY  (`vote_id`),
  CONSTRAINT `user_vote` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`),
  CONSTRAINT `post_vote` FOREIGN KEY (`post_id`) REFERENCES `post` (`post_id`),
  UNIQUE KEY `unq_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

create procedure capture_vote(in_user_id int, in_post_id int) $$
begin
    declare l_current_vote int default 0;

    select vote_id from vote where user_id = in_user_id and post_id = in_post_id into l_current_vote;

    if (l_current_vote = 0) then
        insert into vote (user_id, post_id) values (in_user_id, in_post_id);
    end if;
end 
$$

CREATE TABLE `user` (
  `user_id` int(11) NOT NULL auto_increment,
  `user_name` varchar(64) NOT NULL,
  PRIMARY KEY  (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `post` (
  `post_id` int(11) NOT NULL auto_increment,
  `post_name` varchar(64) NOT NULL,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY  (`post_id`),
  CONSTRAINT `user_post` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `vote` (
  `vote_id` int(11) NOT NULL auto_increment,
  `user_id` int(11) NOT NULL,
  `post_id` int(11) NOT NULL,
  PRIMARY KEY  (`vote_id`),
  CONSTRAINT `user_vote` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`),
  CONSTRAINT `post_vote` FOREIGN KEY (`post_id`) REFERENCES `post` (`post_id`),
  UNIQUE KEY `unq_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

create procedure capture_vote(in_user_id int, in_post_id int) $$
begin
    declare l_current_vote int default 0;

    select vote_id from vote where user_id = in_user_id and post_id = in_post_id into l_current_vote;

    if (l_current_vote = 0) then
        insert into vote (user_id, post_id) values (in_user_id, in_post_id);
    end if;
end 
$$