Help with db schema?

Question

I am creating a simple blog app and so far I have 3 tables, Posts, Comments, and Authors.

I will just list the primary key for each table as well as a foreign key.

Posts will contain postid as a primary key.

Comments will contain commentid as a primary key and postid as a foreign key.

Posts has a 0 to many relationship with comments.

Authors will contain an authorid as a primary key.

Authors will have a many to many relationship with posts and a many to many relationship with comments.

The last statement is where I am having the most trouble. Does authors actually have a many to many relationship with posts and comments since or is it a one to one. If it is a many to many, I have heard that it is a good idea to have an in-between table describing the relationship between posts-authors and comments-authors, but I am not sure what keys I would use in this table?

If I verbally express it as an author can write many posts and many posts can be written by one author, I see it as a one to many, but if I view it from the actual data stored in the tables such as posts will contain multiple posts and authors will contain multiple authors, then it seems like a many to many, so is this what the cross-reference table is for, to remove duplicates.

Answer 1

The cross-reference table in between would look something like this:

create table PostsToAuthors (
    postToAuthorsId int primary key,
    postId int,
    autorId int
);

Each association between an author and a post would require that a record is created in this table. Note that this table does not include the constraints (foreign key and unique specifically) that you would want on a table like this.

Answer 2

No it's just a one-to-many. A post or comment can only have one author - right?

So you would just need a foreign key AuthorID in the post and comments tables

Answer 3

I would think Authors to posts/comments would be a 1-to-many relationship, unless you plan to support a situation where a post could have multiple authors. Maybe that makes sense, but seems unlikely many people would use that. I wouldn't bother complicating my schema for a feature that maybe 0.1% of my users would want.

Just put the authorId on the Posts and Comments table and you should be good to go.

Answer 4

Unless you are running a peculiar kind of forum, surely the rules are

• an Author can write many Posts
• a Post must have one and only one Author
• an Author can write many Comments
• a Comment must have one and only one Author

Answer 5

I would add one more column to tables Posts and Comments to store foreign key of author. Usually, posts and comments have only one single author.

So your schema would look like this:

Posts
    postid - primary key
    authorid - foreign key
Comments
    commentid - priamry key
    postid - foreign key
    authorid - foreign key
Authors
    authorid - primary key

Authors-comments - one(zero) to many relationship
Authors-posts - one(zero) to many relationship
Posts-comments - one(zero) to many relationship

Answer 6

Authors will have a many to many relationship with posts and a many to many relationship with comments.

So you're saying that each author has many posts, and each post has many authors? And the same for comments?

If you're making a wiki, OK, but in most cases, a given post or comment would only have one author. Is that what you meant?

In any case, let's say there is an actual many-to-many relationship. You would create a relational table, for example, called authors_posts, and it would have the following columns: id, author_id, post_id. Each row indicates that "author X and post Y are related."

Answer 7

If I verbally express it as an author can write many posts and many posts can be written by one author, I see it as a one to many, but if I view it from the actual data stored in the tables such as posts will contain multiple posts and authors will contain multiple authors, then it seems like a many to many, so is this what the cross-reference table is for, to remove duplicates.

I see where you're getting confused. "The posts table will contain multiple posts" and "the authors table will contain multiple authors" is true, but that's not the relationship that you're trying to express. (That's just the nature of tables - an authors table will have many rows, each of which represents a single author.)

The crucial thing is, for a given post, how many authors will it have? One, or many? For a given author, how many posts will it have? One, or many?