Database Design

Question

I am making a webapp right now and I am trying to get my head around the database design.

I have a user model(username (which is primary key), password, email, website) I have a entry model(id, title, content, comments, commentCount)

A user can only comment on an entry once. What is the best and most efficient way to go about doing this?

At the moment, I am thinking of another table that has username (from user model) and entry id (from entry model)

   **username    id**
    Sonic        4
    Sonic        5
    Knuckles     2
    Sonic        6
    Amy          15
    Sonic        20
    Knuckles     5
    Amy          4

So then to list comments for entry 4 it searches for id=4.

On a side note: Instead of storing a commentCount, would it be better to calculate the comment count from the database each time when needed?

Answer 1

What exactly do you mean by

entry model(id, title, content, **comments**, commentCount)

(emphasis mine)? Since it looks like you have multiple comments per entity, they should be stored in a separate table:

comments(id, entry_id, content, user_id)

entry_id and user_id are foreign keys to respective tables. Now you just need to create a unique index on (entry_id, user_id) to ensure user can only add one comment per entity.

Also, you may want to create a surrogate (numeric, generated via sequence / identity) primary key for your users table instead of making user name your PK.

Answer 2

Even though it isn't in the question, you may want to have a userid that is the primary key, otherwise it will be difficult if the user is allowed to change their username, or make certain people know you cannot change your username.

Make the joined table have a unique constraint on the userid and entryid. That way the database forces that there is only one comment/entry/user.

It would help if you specified a database, btw.

Answer 3

1. I wouldn't use the username as a primary ID. I would make a numeric id with autoincrement
2. I would use that new id in the relations table with a unique key on the 2 fields

Answer 4

Your design is basically sound. Your third table should be named something like UsersEntriesComments, with fields UserName, EntryID and Comment. In this table, you would have a compound primary key consisting of the UserName and EntryID fields; this would enforce the rule that each user can comment on each entry only once. The table would also have foreign key constraints such that UserName must be in the Users table, and EntryID must be in the Entries table (the ID field, specifically).

You could add an ID field to the Users table, but many programmers (myself included) advocate the use of "natural" keys where possible. Since UserNames must be unique in your system, this is a perfectly valid (and easily readable) primary key.

Update: just read your question again. You don't need the Comments or the CommentsCount fields in your Entries table. Comments would properly be stored in the UsersEntriesComments table, and the counts would be calculated dynamically in your queries (saving you the trouble of updating this value yourself).

Update 2: James Black makes a good point in favor of not using UserName as the primary key, and instead adding an artificial primary key to the table (UserID or some such). If you use UserName as the primary key, allowing a user to change their user name is more difficult, as you have to change the username in all the related tables as well.

Answer 5

It sounds like you want to guarantee that the set of comments is unique with respect to username X post_id. You can do this by using a unique constraint, or if your database system doesn't support that explicitly, with an index that does the same. Here's some SQL expressing that:

CREATE TABLE users (
    username VARCHAR(10) PRIMARY KEY,
    -- any other data ...
);

CREATE TABLE posts (
    post_id INTEGER PRIMARY KEY,
    -- any other data ...
);

CREATE TABLE comments (
    username VARCHAR(10) REFERENCES users(username),
    post_id INTEGER REFERENCES posts(post_id),
    -- any other data ...
    UNIQUE (username, post_id) -- Here's the important bit!
);

Answer 6

Here's my recommendation for your data model:

USERS table

• USER_ID (pk, int)
• USER_NAME
• PASSWORD
• EMAIL
• WEBSITE

ENTRY table

• ENTRY_ID (pk, int)
• ENTRY_TITLE
• CONTENT

ENTRY_COMMENTS table

• ENTRY_ID (pk, fk)
• USER_ID (pk, fk)
• COMMENT

This setup allows an ENTRY to have 0+ comments. When a comment is added, the primary key being a composite key of ENTRY_ID and USER_ID means that the pair can only exist once in the table (IE: 1, 1 won't allow 1, 1 to be added again).

Do not store counts in a table - use a VIEW for that so the number can be generated based on existing data at the time of execution.