Unanswered: Database Design for group-member-vote site
I'm trying to design a mysql database with the following contraints:
A)There will be users associated with a public voting group and/or private group(s):
Users_Table with (iUserId primary key).....and the usual user info (email, first, last, zip, etc...)
B)Users will be members of 1 to many groups (1 public, the rest private)
Groups_Table (iGroupId primary key)
Users_Groups_Table (iUserId + iGroupId composite primary key)
C)Each user can vote on different topics inside of each independent group (and could vote the opposite way inside another group)
c1)Each vote can have a different vote type (state vote, region vote, zip code vote)
c2)Each vote type will usually have a yes/no vote....but could just include a candidate name.
Vote_Table(iVoteId primary key + iUserId + iGroupId + voteType + voteChoice)
D)All users across the site are voting on the same large complete set of topics (public) or a sub-set of those topics (private).
VotingTopicsTable (iVotingTopicId primary key + votingTopic A vs votingTopicB)
As I'm new to database design, I'm looking for a well structured or optimal solution for potential future growth in the users to vote to vote-type to group table infrastructure.
1. The site has 1 to many groups.
1. Each group has a topic list with several vote types per topic depending on the topic. (yes/no/candidate name)
1. Each user can have several different groups that are private from each other
1. Each user in each group can have several different vote types per group per topic