data model and app logic question?

Question

basically i have this problem i have a table of posts(in my mysql database)

Post{user_id, post, vote_up(int), vote_down(int)}

because users log in, then they get to vote, but i wanted to check if the user has already voted, so then i can not allow them to vote again, and obviously im going to be using user session to control this oppose to tracking ips etc.

so i was thinking do i have to create another table or is thier a better approach.

Answer 1

You will need another table e.g. called "votes":

Vote{user_id, post_id}

I assume, that your "Post" table has a primary key (e.g. "id") that you have not shown in your question above? Then "post_id" should be a foreign key to Post#id.

/Carsten

Answer 2

Perhaps you should look at how it was done here

Answer 3

You'll definately need another table, and some primary and foreign keys would help too:

Post{post_id(int), user_id(varchar), post(clob)}
Votes{vote_id(int), post_id, user_id, up_down(char)}

Your vote_up and vote_down column values are removed and are now calculated with queries:

-- vote_up
select count(*) from votes where post_id = n and up_down = '+';
-- vote_down
select count(*) from votes where post_id = n and up_down = '-';

There should be a unique index on votes(post_id, user_id) to prevent multiple votes by the same user on the same post.
The primary key vote_id does not have to be defined, but each table should have a primary key and if you don't want to use a surrogate key, then you can define the PK using the same columns as above and this will serve as the unique index too, so ot does not have to be defined.