Best Practices in setting up a Blog data model

Question

I have the following setup for my Blog Data Model:

Blog               Articles             Categories
-----              --------          1  ----------
ID  \ 1         *  ID            /----- ID
Name \------------ BlogID       /       Name 
Owner              Name      * /      
                   CategoryID-/

I'm worried about how this data model matches the 'best practices' for Blogs. If a Blog is deleted, should all the articles for that blog be deleted? If a Category is deleted, what should happen to the Articles?

Is there a better way? Is there something I'm missing? What do 'best-practices' suggest to do?

Answer 1

Well, your foreign key (which should exist) relating your blog category to your blog post wouldn't allow you to. You should consider change the category of your article to a different one if you want to keep your article and referential integrity.

Answer 2

Depends. If your blog engine does not allow an entry without an specified category, you should probably notify the user of the consequence and delete the articles. Otherwise you could use an ON DELETE SET NULL instead.

Answer 3

If your relationships are set up right, you shouldn't be able to delete the category without first deleting all related data. It does seem weird that deleting a category would remove a bunch of articles, but at the same time, if a category is no longer valid, then the articles shouldn't be either.

Perhaps an option to remap all articles to a new category.

Answer 4

A couple of questions. Can an article have more than one category? Are you allowing articles to have 0 categories associated with them? By your description though, I'd say that you don't want to delete the articles. The categories, in my experience when developing a blog, are usually a many to many relationship with articles/posts (multiple categories describing multiple posts). So I wouldn't delete them, but once again it would depend on your business logic and how you wanted to handle it.

Answer 5

This really depends on your use case. A lot of blog applications use tags with a many-to-many relationship instead of categories with a one-to-many relationsip because it makes more sense to see an article as related to (possibly) many tags than to see an article as "contained" within a category.

If the logic of your blog is that an article is strictly contained in a category, then the one-to-many relationship is justified. And in some circumstances a cascading delete would also be justified. After all, where would the orphaned articles even exist.

This is just a case where you must anticipate your user-base's intuition. What would they assume is natural.

Incidentally, it's a lot more work, but if it were me, I wouldn't do a cascading delete. I would mark articles as orphaned and hide them from most views. I would also provide some interface for restoring orphaned articles. And I would consider providing a script that deletes anything that's been orphaned for more than some period of time. That way, if you were wrong about what your users expected to happen, you can fix the problem.

Answer 6

Forget about data model , use Object Model, that is what data is implementation, and object is interface, which is closer to the requirement.

Answer 7

This (older) post introduces some tag related db schema variations including a detailed discussion. Maybe it's a good starting point.