Database Schema discussion: Voting App

Database Schema discussion: Voting App
0

#1

I’d like to bounce some ideas off anyone with a little Mongo nous.

The basic requirements are:

  • Provide polls
  • A poll can have any number of options
  • Users can vote once per poll
  • Users can add new options to polls
  • A results page can pull the data together showing how many votes were cast for each option

Given that the polls are user generated and can have various numbers of options, I’m having a little trouble committing to a database design - I have three options I’m thinking of (expand the details for each db design):

Relational DB Style
## User table
  [pk] userid
  username (email address)
  datestamp

## Polls table
  [pk] pollid 
  [fk] userid 
  poll title
  datestamp 

## Votes table
  [pk] voteid
  [fk] pollid
  [fk] userid
  vote detail
  datestamp
JSON style (options and voteCounts as array of objects)
{
    poll: 'pollTitle',
    creator: 'username',
    createdTime: new Date().toISOString(),
    options: [
        { 
            option: 'option1',
            voteCount: 0
        }, 
        { 
            option: 'option',
            voteCount: 0
        }
    ], 
    voters: ['user1', ...], // as many voters as required   
}
JSON style (options and voteCounts as separate arrays)
{
    poll: 'pollTitle',
    creator: 'username',
    createdTime: new Date().toISOString(),
    options: ['option1', 'option2', ...] 
    voteCount: [0, 0, ...], 
    voters: ['user1', ...],
}

I suspect the JSON versions are more like the idiomatic Mongo way of doing things (but my previous experience with RDBMSs made me lean towards the 3xTable version intuitively).

Of those, I think I like the second JSON version better, but I’ll need to be mindful that the options and vote counts can’t be accidentally sorted / rearranged.

SO… am I on the right track, or barking up the wrong tree? Or bikeshedding?