Yesterday I wrote about deciding to build a quick and dirty voting solution. Or to try to at least! Should be interesting to see how I fare with you all:-)

Here are the tables I ended up with:

As you might guess Ballot holds the overall description of what is being voted on, BallotDetail holds the options that voters can pick from. Here’s the DDL for those two:

CREATE TABLE [Voting].[Ballot](
    [BallotID] [uniqueidentifier] NOT NULL,
    [DateAdded] [datetime] NOT NULL,
    [Title] [varchar](50) NOT NULL,
    [Description] [varchar](4000) NOT NULL,
    [OpeningDate] [datetime] NULL,
    [ClosingDate] [datetime] NOT NULL,
    [MinSelection] [tinyint] NOT NULL,
    [MaxSelection] [tinyint] NOT NULL,
CONSTRAINT [PK_Election] PRIMARY KEY CLUSTERED
(
    [BallotID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [Voting].[BallotDetail](
    [BallotDetailID] [uniqueidentifier] NOT NULL,
    [DateAdded] [datetime] NOT NULL,
    [Title] [varchar](75) NOT NULL,
    [BallotID] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_BallotDetail] PRIMARY KEY CLUSTERED
(
    [BallotDetailID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [Voting].[BallotDetail]  WITH CHECK ADD  CONSTRAINT [FK_BallotDetail_Ballot] FOREIGN KEY([BallotID])
REFERENCES [Voting].[Ballot] ([BallotID])
GO

ALTER TABLE [Voting].[BallotDetail] CHECK CONSTRAINT [FK_BallotDetail_Ballot]
GO

ALTER TABLE [Voting].[BallotDetail] ADD  CONSTRAINT [DF_BallotDetail_BallotDetailID]  DEFAULT (newid()) FOR [BallotDetailID]
GO

ALTER TABLE [Voting].[BallotDetail] ADD  CONSTRAINT [DF_BallotDetail_DateAdded]  DEFAULT (getutcdate()) FOR [DateAdded]
GO

HQ would load those two tables first, then load the list of eligible voters into Voting.EligibleVoters where they get assigned a “voterid” in the form of a uniqueidentifier. Eventually we will send a email to each eligible voter where the url looks something like “pass.org/vote.aspx?ballotid=X&voterid=Y. You’ll notice I cheated some, adding a HasVoted flag here rather than just determining it based on whether they have actually cast a vote:

From there votes get inserted into VotesCast:

For the remaining tables, VoterInquiries is used to log when voters arrive at the site only knowing a ballotid, giving us the ability to help out by providing a generic link like “pass.org/vote.aspx?ballotid=x” and then they can enter their email address – if it matches we send them a full voting link.

VotingTemplates has only one row for now, the HTML template we send to voters. It contains tokens that we fill in to show the ballot name, URL, etc. Finally, the NotificationSchedule table allows us to set up a series of emails to remind voters for each ballot to vote.

I can’t say I agonized over the design. I used uniqueidentifiers because I planned to pass them around in the URL querystring (which ruled out using newsequentialid()) and I used a schema other than dbo (Voting) so that if we like the solution we can just add it to the main PASS db rather than having a separate Voting db. I tried to set reasonable primary keys and foreign keys, but haven’t done any load testing (that time thing again!). While I might be missing an index or two, given the low volume of data and low number of concurrent users, I’m not expecting any tough performance issues.

I’ve uploaded scripts for all the tables here. In the next post we’ll look at the data access pieces on the server.