0

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

2
  • 1
    I'm not sure I understand you here. Will listitem contain one of the values (e.g. either one or 2 or 3 or 4) or comma separated values?
    – Tom V
    Feb 12, 2016 at 11:49
  • Sorry, each List will have a specified amount of items. E.g. 5 items or 10 items. Then the votes will dictate what order they are. SO even tho the submitter may choose something as the "number one" the votings could infact push that down to be number 10. Does this make more sense? I just need a way for votes to be able to be cast against each item within the list. Then the amount of votes I can use to order the list :)
    – InchHigh
    Feb 12, 2016 at 12:04

2 Answers 2

0

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.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.