Database Design – How to Design a Database for a Voting Application

databasedatabase-designrelational-database

I'm working on a smallish voting webapp. Similar to strawpoll but only authenticated users are allowed to make polls, while everyone is allowed to vote.

User:

id
email/password_hash/name/…some other data…

Poll:

id
user_id REFERENCES User.id – notes which user created the poll
title

PollOptions:

id
text
poll_id REFERENCES Poll.id – notes which poll the options belongs to
count – Number of votes for this option

Votes:

id(optional?)
poll_id REFERENCES Poll.id – notes which poll was voted on
user_id REFERENCES User.id – notes the user which voted
ip_address – If the the person who voted isn't registered, ip_address will be filled to prevent him from voting multiple times.

I want the owner of the poll to be able to delete polls and change the poll (add more options, remove options, … ) Is my design good enough? If not, what should I change?

Best Answer

Entity names should be singular, POLL_OPTION, not POLL_OPTIONS.
VOTE should reference POLL_OPTION, not POLL
FK from VOTE to USER must be not mandatory (allow null) to allow non-registered users to vote.
COUNT is a calculated field, it's either not necessarry or it has to be updated post-poll by counting rows in VOTE for every option.
Take in mind that when you delete a POLL you must do it in cascade or the deletion will fail if polls already have options, and options already have votes.
PK of POLL_OPTION should be (POLL_OPTION_ID,POLL_ID), that way FK from VOTE to POLL_OPTION will have to be also (POLL_OPTION_ID,POLL_ID). That will allow you to make (USER_ID, POLL_ID) the PK of VOTE. That way the same person will not be able to vote twice in the same POLL. If you are into surrogates, then add VOTE_ID, make it the PK but also add an unique index on (USER_ID, POLL_ID) to achieve the same constraint.

UPDATE:

Since OP stated that keeping who voted for what option to be kept secret, here's another solution:

VOTE has PK to POLL as OP's original design. Only a vote per person is allowed via PK and or unique index on EMAIL_ADDR.
But another entity called BALLOT, not related with USER, will hold a row for each vote. The results of the poll will be counted from the rows on this table. If you multiple users were to update the COUNT in the same row at the same time you will have blocking problems.
After the poll is finished you can calculate totals by counting the ballots and update the COUNT on POLL_OPTIONS. Then you can either delete the ballots or keep them for audit.

Related Solutions

Database – Designing a Database Application with OOP

In modern SQL Server versions (> 2008 IIRC) you can use User defined types. So your sproc can actually take an array of user types:

http://blogs.msdn.com/b/felixmar/archive/2010/10/27/how-to-create-and-execute-a-stored-procedure-using-a-table-as-a-parameter.aspx

In our app we actually use the repository pattern, but hack it a bit to get around slowness issues like you describe.

So, in your example, I'd do something like this:

using (CustomerBulkContext ctx = customerCollection.GetSaveContext())
{

foreach(Customer c in customerCollection)
{
   if(c.ShouldSave())
   {
       c.Save(ctx);
   }
}
ctx.Save();
}

The idea is that all the customers are added to an in-memory list inside the context, and then the actual commit to the DB is handled via the save context's .Save() method. That method then creates a list of SQL objects and hands them to a sproc that does the bulk update/insert/delete.

Database Design – Designing a Database with Circular References for One-of-Many Case

For a simple project I would probably adopt the second solution. On the other hand, first solution may be slightly modified to avoid your mentioned drawbacks. You could model a profile image as a special case of an image, i.e. inheritance in OOP or is-a hierarchy in relational DB.

Therefore, leaving out the profile_image column and introducing table

Profile image:
   image_id - foreign key to Image
   user_id - foreign key to User

with unique constraint on user_id and possibly dedicated primary key column + indexes on columns used for the desired use case.

Only problem may be the sorting, where you'd need keep some weights (a column in Image table) in consistence with Profile image table.

Best Answer

Related Solutions

Database – Designing a Database Application with OOP

Database Design – Designing a Database with Circular References for One-of-Many Case

Related Topic