SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 DB Design Doubts
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

simflex
Constraint Violating Yak Guru

319 Posts

Posted - 03/07/2014 :  09:23:21  Show Profile  Reply with Quote
We are trying to build an online ballot box that allows some individuals from various groups to contest for posts.

Three organizations are involved in the ballots.

I have listed their names below

EAEB
CB
GRC

In one of those organizations called EAEB, there are six positions. I have listed these positions below.

President
First_VP
Second_VP
Third_VP
Secretary
Treasurer

Some voters can vote in all organization elections but some cannot.

ONLY the members of EAEB organizations can vote for ALL elections including EAEB.

If a voter is not a member of the EAEB organization, that member cannot in EAEB elections but can vote in the other two elections.


The remaining two organizations don't have positions, just have members.

Voters are just asked to elect x numbers of members to serve as members in the CB and GRC organizations.

Some members of EAEB are either Retired or still Active.

We would like to indicate if the individual running for office in EAEB organization is an incumbent.

Below is the design I have come up with.

Could someone please look the tables over and recommend if they are ok or what additional tweaks I may need?

Many thanks in advance

Organizations 
 ( 
   OrgID int /*identity*/ NOT NULL, 
   Name varchar(60) NOT NULL 
  )

Positions 
 ( 
   PositionID int /*identity*/ NOT NULL, 
   OrgID int NOT NULL, 
   Title varchar(50) NOT NULL, 
   CurrentOfficeHolder PersonId NULL 
  )

Persons 
 ( 
   PersonID int/*identity*/ NOT NULL, 
   FirstName varchar(50) NULL, 
   LastName varchar(80) NOT NULL 
 )

Members 
 ( OrgID, PersonID, 
   is_eligible_to_hold_office bit NOT NULL 
 )

Voters 
 ( OrgID, 
   PersonID, 
   status smallint NOT NULL 
 )

Votes 
( VoteID int /*identity*/ NOT NULL, 
  OrgID, 
  PositionID, 
  date NOT NULL, 
  status smallint 
)

Voting_Results 
( 
  VoteID, 
  PersonID, 
  has_voted bit NOT NULL, 
  is_yea bit NULL, 
  is_nay bit NULL, 
  is_abstain NULL 
)
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000