1

I'm trying to create a normalized database for a blog allowing reply to comments. Given a few answers I found, it seems that I need to look at the adjacency list model and the modified preorder tree traversal algorithm. However, after reading a bit about it, I haven't found an example of it using foreign keys to enforce data integrity. Can it be done?

What database design do you recommend in this case? Ideally, I'd like to be able to eliminate a parent comment, and by using PK-FK relationships (PK = primary key, FK = foreign key) also be able to eliminate all the child comments in order to avoid keeping orphans in the table.

UPDATE: As a clarification, I'd also like to know which database design is used in blogs that allow reply to comments (that is, comments that reply to a comment that reply to an original thread).

r_31415
  • 8,752
  • 17
  • 74
  • 121

3 Answers3

1

Unless you are using cascading deletes (not recommended), you will need to walk the tree from top to bottom, then start deleting nodes from the bottom. You could do this traversal in the app layer or the database layer (i.e. in a stored proc), but either way all the deletes should be rolled into a database transaction.

I haven't found an example of it using foreign keys to enforce data integrity. Can it be done?

I'm not clear on what you are asking here. The whole model pretty much relies on a PK/FK relationship (between a comment and its parent). The article you link doesn't say so explicitly (that I can see), but the title column would be the PK, and the parent column would be the FK.

Phil Sandler
  • 27,544
  • 21
  • 86
  • 147
  • Actually, I thought I needed to use cascading deletes. Why is it not recommended?. W.r.t. the model relying on PK/FK relationship, you're right but if a parent can have many children, when you eliminate a parent (which is a PK), its children will be removed from that table, but I was wondering what kind of database design would allow to delete those children from another table (maybe a 'comments' table) but that would be using cascading deletes. Given that it's not recommended, is it fine to delete those orphan children with a query via PHP? – r_31415 Jul 21 '11 at 22:03
  • Re: cascading deletes on the database--do some searches on the topic. I generally don't use them, but your scenario may be simple enough that they don't cause problems. I think if you want pure real nesting of comments, you need an adjacency list. The only other option I can think of is the "nested set" model (which some people love--I personally hate it). I'm still not sure I'm clear on your Pk/FK question. – Phil Sandler Jul 21 '11 at 22:18
  • Thanks for your response. Well, I will look at that. As for the PK/FK question, basically I was asking what kind of database schema would allow to keep data integrity when you delete a parent comment since the adjacency model, for example, provides a model to order elements of a table hierarchically and retrieve them but by itself it's not enough in the case of a blog with a 'reply to comments' feature. – r_31415 Jul 21 '11 at 22:29
1

Reddits source code is open source on Github, see how they do it....

Cody Caughlan
  • 32,456
  • 5
  • 63
  • 68
0

Yes, MySQL supports Foreign Keys and cascading deletes as part of its database storage engine. You will need to use InnoDB which is not the default. Changing the storage engine is really easy and can be done even after tables are created.

MySQL official documentation:

http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

Alteration:

ALTER TABLE table1 ENGINE=InnoDB;

There are additional pros and cons to using an InnoDB engine vs a MyISAM engine(MySQL storage engine default). So you should carefully research the two before putting it in a production environment. MyISAM does not support Foreign Keys.

Edgar Velasquez Lim
  • 2,426
  • 18
  • 15
  • nor does MyISAM support multicolumn indexes (though it lets your set more than 1 column as key), recovery from crash can take hours to days even if table is large, table corruption after crash, no transactions, table level-locking, no multiversion concurrency control and a myriad of other things. – bash- Jul 22 '11 at 01:50