Designing tables for Comments to Articles

Databases
1

I want to allow registered users to be able to add comments to articles on my website. In addition, registered users can “vote” on other people’s comments, however they can only vote once per other comment.

It would be easier to display an ERD, but here is what I have in text terms…

  • One and only one ARTICLE can have zero to many COMMENT

  • One and only one MEMBER can have zero to many COMMENT

  • One and only one COMMENT can have zero to many VOTE

  • One and only one MEMBER can have one and only one VOTE on any given COMMENT

I believe this design is correct, however, I am totally unsure of of how to model things so that a MEMBER can only VOTE once on any given COMMENT?!

Thanks,

Debbie

2

Create a UNIQUE constraint on (member_id, comment_id) in the VOTE table. A second row with the same pair can’t be inserted.

3

aw, heck, just make that the primary key

:cool:

4

I don’t follow you?! :-/

The PK of any table is always “unique”.

But ensuring that CommentID + MemberID is unique would appear to ensure that a Member doesn’t vote multiple times on the same Comment.

Debbie

5

bingo, you gots the idea :smiley:

so make CommentID + MemberID the PK

PKs are, as you know, unique by definition

you don’t need anything else

or did you, perhaps, think that there was gonna be an auto_increment PK for this table? because that would be wasteful and unnecessary

6

I guess I was always taught to create an “ID” for ever table that was auto increment.

So you’re saying don’t do that?

Debbie

7

yes, don’t do that :slight_smile:

create an auto_increment id as the primary key only if the table does not have a suitable natural key

in the case of a relationship table, the two foreign key columns taken together are a perfect primary key, and having an auto_increment id is counter-productive

8

But I just realized there is a problem… :eek:

The COMMENT table has a PK made up of article_id and member_id.

The MEMBER table has a pk made up of member_id.

So how can I have a composite key for the VOTE table as it would be…


PRIMARY KEY(article_id, member_id, member_id)

:blush:

Debbie

9

i guess you haven’t actually created these tables yet?

because if a comment belongs to an article and is also associated with a member (the one who made the comment), then the PK of the comment table would be article_id plus member_id

so if other members can vote on the comment, therefore you need a second member_id in the vote table

so the PK of the vote table would be article_id plus member_id (who wrote the comment) plus member_id (who made the vote)

so your vote table would have two member_id columns, and you wouldn’t of course be able to give both of these columns the name “member_id”

in a similar way, in a database which keeps track of sports games, the games table has two team_id columns, one is often called home_team_id and the other away_team_id

10

you’re absolutely right

you’re absolutely wrong, it’ll be bigger

what, you’re proposing that the comment_id, member_id, and created_datetime be dropped from the table?

tee hee :smiley:

11

Yes, I did last night, but wasn’t getting it.

because if a comment belongs to an article and is also associated with a member (the one who made the comment), then the PK of the comment table would be article_id plus member_id

Right.

so if other members can vote on the comment, therefore you need a second member_id in the vote table

so the PK of the vote table would be article_id plus member_id (who wrote the comment) plus member_id (who made the vote)

so your vote table would have two member_id columns, and you wouldn’t of course be able to give both of these columns the name “member_id”

in a similar way, in a database which keeps track of sports games, the games table has two team_id columns, one is often called home_team_id and the other away_team_id

Okay, I wasn’t seeing that!

Here is a dump of my tables…

article
Field Type Null Default Comments
id mediumint(8) No
title varchar(250) No
subtitle varchar(250) Yes NULL
article_date date No
author varchar(100) Yes NULL
body text No
end_notes text Yes NULL
created_on datetime No
updated_on datetime Yes NULL

comment
Field Type Null Default Comments
article_id mediumint(8) No
member_id mediumint(8) No
body text No
status varchar(20) No
created_on datetime No
approved_on datetime Yes NULL
updated_on datetime Yes NULL

member
Field Type Null Default Comments
id mediumint(8) No
email varchar(40) No
pass char(40) No
first_name varchar(20) No

vote
Field Type Null Default Comments
article_id mediumint(8) No
member_id mediumint(8) No
voting_member_id mediumint(8) No
score tinyint(4) No
created_on datetime No
updated_on datetime Yes NULL

Indexes:
Keyname Type Cardinality Field
PRIMARY PRIMARY 0 article_id, member_id, voting_member_id

How does that look?

Debbie

12

not bad at all

i personally would not use member_id for one of the columns, i would choose a role-based name for both of them, as in member_commenting and member_voting (similar to home_team and away_team for the team_ids in a game table)

as for showing the table structure, it’s far better to copy the results of a SHOW CREATE TABLE statement rather than copying/pasting from a phpmyadmin display which is what it looks like you did – SHOW CREATE TABLE is much more comprehensive

13

Good.

i personally would not use member_id for one of the columns, i would choose a role-based name for both of them, as in member_commenting and member_voting (similar to home_team and away_team for the team_ids in a game table)

Good idea.

as for showing the table structure, it’s far better to copy the results of a SHOW CREATE TABLE statement rather than copying/pasting from a phpmyadmin display which is what it looks like you did – SHOW CREATE TABLE is much more comprehensive

Okay.

Thanks,

Debbie

14

I just realized another small problem…

How do I allow a MEMBER to make multiple COMMENTs for a given ARTICLE?

If the pk for COMMENT is (article_id, member_id) then there could only be one comment.

Should I make the pk for COMMENT a new comment_id?

Or could I append the create_date to form a new pk of (article_id, member_id, create_date)?

Debbie

15

since you have created_on in the table anyway, adding it to the PK isn’t like adding an additional table column, it’s a freebie, and it solves the PK issue nicely

:slight_smile:

16

Such a complicated PK is hard to pass around to do things like moderate, delete, reply to comments, though. Every URL/form would need to include an article ID, member ID and a timestamp. An auto increment column would make those things easier and make the votes table smaller.