CODEWITHSUNDEEP
mysqlrubydatabaseschemavoting
user1263500
user1263500

Reputation: 83

How do you structure a database that allows voting similar to how stackoverflow does it?

I'm new to databases and web development but I'm trying my best to learn my way though making my own dynamic website. I'm taking it step by step and am currently designing the data model on paper. I'm wonder how does one structure a database for a website that allows voting like how stackoverflow does it? If there's a table that contains a list of questions, each question that a user creates gets added to this table. There can't simply be a field on this table that counts votes because that would allow a single person to have unlimited votes right? So there should be a key that connects to another table which counts votes and keeps track of users so they can't vote twice, correct? If this is true, this is the part where I get confused. Each answer given can also be voted on. So does that mean that when a user submits an answer, in addition to adding that answer to probably a separate table for answers per question asked, the model must also generate a new table for each answer dynamically during run-time to keep track of all these votes?

Note that I'm not specifically asking about how stackoverflow does it, but how the concept of what the user experiences works.

One thing I'd also like to do is query the activity of a single user, so if all these tables would have to be created dynamically for every piece of submitted data, creating a crap load of tables over time, wouldn't it be really slow to have to parse through every table to check if a particular user submitted any data or voted?

Is there a better way of doing this that someone could explain in laymen terms? No need for specific code... I can probably figure that out later when the time comes. I'm just theorizing right now and building a paper model to work off of later.

EDIT: Oh, I see. I think in excel-like spreadsheets when I'm thinking of database tables, so correct me if my understanding is wrong. So every vote site-wide is on a single table (listed vertically on a spreadsheet), each having a line of data (horizontally) that links the vote to a variety of owners (user and question OR answer)? Is that correct? I say question OR answer because I don't understand a scenario where it would make sense to have them both as a vote attribute(not sure if that's correct terminology) instead of creating two separate vote datas for an answer and a question which are both being voted on. Basically the way I see it, each line represents a vote and there are 3 fields, 1. Value (+1 or -1), 2. From whom (username), 3. To what (question or answer).

Upvotes: 7

Views: 3085

Answers (3)

Steven Spungin
Steven Spungin

Reputation: 29109

Use A NOSQL Document Approach. (couchdb)

Database: Stacklike

Documents In Database Stacklike:

{
  "type": "question",
  "user": "<userid>"
  ...
}

{
  "type": "answer",
  "user": "<userid>",
  "question": "<questionid>"
  ...
}

{
  "type": "vote",
  "user": "<userid>",
  "question": "<questionid>",
  "weight": "<weight>"
  ...
}

{
  "type": "user",
  ...
}

Views:

To List Votes, Sorted By Question

map(doc){
  if (doc.type === 'vote'){
    emit(doc.questionid, doc)
}

To View Vote Counts and Totals (stats)

map(doc){
  if (doc.type === 'vote'){
    emit(doc.questionid, doc.weight)
}
reduce(keys,values, rereduce){
  _stats
}

Upvotes: 0

Brian
Brian

Reputation: 2229

You have to look at all the elements. Basically you have 

Questions
Users
Answers
Votes

Users are tied to Questions and answers and votes so you will either need adequate foreign keys to handle this or a child table that connects these. For instance you could have

tblQuestions
    questid
    question
    userid

then

tblAnswer
    Answer
    answerid
    userid
    questid 
    accepted (to flag as accepted answer)

and finally

tblVote
    vote (up or down)
    questid
    answerid
    userid

The user table is fairly straightforward and the fun part happens in the behind the scenes logic. this is obviously a very rough layout and a lot of other things need to be considered and there are dozens of ways to accomplish the table layout.

Upvotes: 4

Hammerite
Hammerite

Reputation: 22340

CREATE TABLE "QuestionVote" (
    "Question" INT NOT NULL, -- To identify the question being voted on
    "User" INT NOT NULL, -- To identify the user who is casting their vote
    "Vote" SMALLINT NOT NULL CHECK ("Vote" = 1 OR "Vote" = -1),
    PRIMARY KEY ("Question", "User"),
    CONSTRAINT "Constr_QuestionVote_Question"
        FOREIGN KEY "QuestionVote_Question" ("Question")
        REFERENCES "Question" ("ID"),
    CONSTRAINT "Constr_QuestionVote_User"
        FOREIGN KEY "QuestionVote_User" ("User")
        REFERENCES "User" ("ID")
)

NB. The answer to a problem in database design is never "Create a new table on-the-fly for every new (user/discussion/item of whatever sort)". If you think it is a good idea to create a new table for every user, you have made a mistake! Stop and work out how you can do what you want to do with a fixed set of tables.

Upvotes: 3

Related Questions