Database Design - Voting System

Question

I'm creating a website in which people can submit there own lists, and then essentially others Up/Down Vote the items within the list. I'd like the list (If the submitter allows "votes") to be ordered by the amount of votes (Easy Enough to do in SQL I know).

However, my issue is with the DB Design, I don't know how to design it to make it work.

So far my Idea is:

Lists Table: ListID, ListDescription, ListItem(x [e.g. 1, 2, 3 4]), CreatedDate, UserID (if it is user submitted).

Votes Table: VoteID, ListID

I can't quite get my head round, how to setup the votes table to record votes for each item in the specified list?

Could anyone help please?

Thanks

Answer 1

Not quite sure about what you're after, but you want a list that is created (or not) by one user and this list can have multiple items. The votes on each of the item will determine what position they are on the list?

I can think of it like this:

• The table Lists contains the data of the list itself and the table ListItems contains the ListID, the Item and the Votes

  Lists: ListID, UserID, ListDescription, CreatedDate
  ListItems: ListID, ItemID, Votes

Or

• ListTable with the data of the List, ListItems with the data of the Items and one initial position for each item and a Votes table, voted by one UserID.

  List: ListID, UserID, ListDescription, CreatedDate
  ListItems: ItemID, ListID, InitialPosition
  Votes: VoteID, UserID, ItemID

Correct me if I didn't understand correctly the problem.

Answer 2

I think I'm understanding basically what you're after.

Are you wanting to record each individual vote or maintain a single value in a +1/-1 fashion? This could be a start anyway:

UserLists Table

Userid ListId VotingAllowed CreationDate

ListItems Table

ListId ListDescription InitialSequence Votes