Clever Geek Handbook

Database design

I am making a webapp right now and I am trying to figure out the design of the database.

I have a user model (username (this is the primary key), password, email, website) I have an input model (id, title, content, comments, commentCount)

A user can only comment on a post once. What is the best and most efficient way to do this?

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

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

So, to list the comments for input 4, it looks for id = 4.

On a side note: Instead of storing a commentCount, would it be better to count the number of comments from the database every time needed?

+2


source to share


6 answers


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

You can 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 on your system, this is a perfectly valid (and easily readable) primary key.



Update : just read your question again. You don't need comment or comment fields in the "Posts" table. The comments will be correctly stored in the UsersEntriesComments table and the counts will be computed dynamically in your queries (saving you the trouble of updating this value yourself).

Update 2 . James Black makes a good argument for using UserName as the primary key and instead adds an artificial primary key to the table (UserID or some such). If you use UserName as the primary key, it makes it more difficult for the user to change their username, since you also need to change the username in all related tables.

+2


source


What exactly do you mean by

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

(my emphasis)? Since it looks like you have multiple comments for each object, they should be kept in a separate table:



comments(id, entry_id, content, user_id)

entry_id

and user_id

are foreign keys to the corresponding tables. Now you just need to create a unique index ( entry_id

, user_id

) so that the user can only add one comment for each object.

Also, you may need to create a surrogate (numeric, generated via sequence / id) primary key for your user table instead of making the username your PC.

+2


source


  • I would not use username as primary identifier. I would do a numeric id with auto-increments
  • I would use this new ID in a relationship table with a unique key in two fields
+2


source


Here's my recommendation for your data model:

USERS table

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

ENTRY table

  • ENTRY_ID (pk, int)
  • ENTRY_TITLE
  • CONTENT

ENTRY_COMMENTS table

  • ENTRY_ID (pk, fk)
  • USER_ID (pk, fk)
  • K.P

This setting allows ENTRY to have 0+ comments. When a comment is added, the primary key, which is a composite key ENTRY_ID

and USER_ID

, means the pair can only exist once in the table (IE: 1, 1 will not allow 1, 1 to be added again).

Do not store accounts in table - use VIEW to do this so the number can be generated from existing data at runtime.

+2


source


Even if it is not, you may want to have a user id that is the primary key, otherwise it will be difficult if the user is allowed to change their username or tell certain people that you cannot change their username.

Make the concatenated table a unique constraint on the userid and entryid. This way the database only forces one comment / post / user.

This will help if you specified a database, btw.

+1


source


It sounds like you want to ensure that a set of comments is unique with respect to username

the X post_id

. You can do this using a unique constraint, or if your database system does not explicitly support it, 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 the important bit!
);
0


source






More articles:


All Articles