Should I capture all the relationships in a Database design?

Question

I am a newbie when it comes to database design. I was trying to create a blogging application and I ran into a problem. My database consists of the following tables:

User
HasInt (Records which user has which interest)
Interest (category of blogs: Sports, Literature etc)
Follow (Records which user follows which user)
Blog
Comment
From what I realise, I should not have anything to do with knowing how many comments have been made for any particular Interest. But I can see that there is a many to one relationship between Comment and Interest. Should I still record this information ?
If so, why ?

John Herbert · Answer

Second what @bbaird says. I usually start with the objects I'll be dealing with. (user, category, blog, comment).
Then I'll start thinking about how they are connected.

A user is interested in zero to many categories
A user can follow another user
A user can create a blog
A user can create a comment
A comment is applied to a blog
A blog belongs to a category

To cover the links that are to one to many or many to many, I'll use joining tables that look like this;
UserToCategory (PK int, UserID int, CategoryID int)
UserToBlog  (PK int, UserID int, BlogID int)
Obviously there's a bit more to fully flesh out the plan, but this gets things started. If your foreign keys are built right, you can run a query to get all comments where the blog category is sports, joining across a couple of tables at once.

Should I capture all the relationships in a Database design?

One Answer

Add your own answers!

Ask a Question