CODEWITHSUNDEEP
mysqldatabaseschemaforeign-keysprimary-key
r_31415
r_31415

Reputation: 8982

Database schema design and foreign keys in MySQL

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).

Upvotes: 1

Views: 844

Answers (3)

Edgar Velasquez Lim
Edgar Velasquez Lim

Reputation: 2446

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.

Upvotes: 0

Cody Caughlan
Cody Caughlan

Reputation: 32748

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

Upvotes: 1

Phil Sandler
Phil Sandler

Reputation: 28016

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.

Upvotes: 1

Related Questions