Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0
This is the forum to discuss the Wrox book ASP.NET 2.0 Website Programming: Problem - Design - Solution by Marco Bellinaso; ISBN: 9780764584640
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old April 30th, 2009, 06:00 PM
Authorized User
 
Join Date: Mar 2009
Posts: 75
Thanks: 16
Thanked 1 Time in 1 Post
Default Relationships between the Tables - Delete rule

Is it wise to set relationship between Categories and Articles tables Delete rule = cascade?

I think that with this dangerous operation by mistakenly deleting a category (even confirming the delete operation) will result a deletion of all articles in a category.

Maybe it's better to cascade to a default value like "Uncategorized"?
And also to add one or 2 SPs for deleting category and all alrticles by demand.
This will of course lower the performance but will increase capabilities.
  #2 (permalink)  
Old April 30th, 2009, 07:15 PM
Lee Dumond's Avatar
Wrox Author
Points: 4,942, Level: 29
Points: 4,942, Level: 29 Points: 4,942, Level: 29 Points: 4,942, Level: 29
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2008
Location: Decatur, IL, USA.
Posts: 923
Thanks: 12
Thanked 166 Times in 162 Posts
Default

You raise an excellent point.

Blocking deletes on categories which have articles assigned to them would probably be safest, if you intend to handle this at the database level.

There is a school of thought that holds that all referential integrity should be exposed by the DAL and not buried in the database at all, which I tend to agree with. If you did that, it would be easy to establish rules like what you've suggested:

"When deleting a category, if any Articles belong to that Category, set those Articles to Uncategorized".
__________________
Visit my blog at http://leedumond.com
Follow me on Twitter: http://twitter.com/LeeDumond

Code:
if (this.PostHelpedYou)
{
   ClickThanksButton(); 
}
  #3 (permalink)  
Old May 1st, 2009, 05:00 AM
Authorized User
 
Join Date: Mar 2009
Posts: 75
Thanks: 16
Thanked 1 Time in 1 Post
Default

What should be the actual implementation?
I was thinking about 2 options:

1)Create a new category "Uncategorised" - make it protected against
deletion operation.Then assign id of this category as default value to
CategoryId column in Articles table.
Finally set the relationship delete rule to "Set Default"

2)Without creating any category, just assigning a default value(maybe 0 or
-999).
When articles are listed, we check if it assigned to category with id 0 (so
there is no matched category in category table for this id) and manually
set "Uncategorised" caption.


*Both options would have SP DeleteCategoryWithArticles(categoryId)
  #4 (permalink)  
Old May 1st, 2009, 09:44 AM
Lee Dumond's Avatar
Wrox Author
Points: 4,942, Level: 29
Points: 4,942, Level: 29 Points: 4,942, Level: 29 Points: 4,942, Level: 29
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2008
Location: Decatur, IL, USA.
Posts: 923
Thanks: 12
Thanked 166 Times in 162 Posts
Default

I think most blogs and such use option 1 or a variation thereof. There is usually an built-in default Uncategorized category (that cannot itself be deleted, of course) that any post will belong to unless it's assigned to a real category.
__________________
Visit my blog at http://leedumond.com
Follow me on Twitter: http://twitter.com/LeeDumond

Code:
if (this.PostHelpedYou)
{
   ClickThanksButton(); 
}
The Following User Says Thank You to Lee Dumond For This Useful Post:
yevi (May 1st, 2009)


Similar Threads
Thread Thread Starter Forum Replies Last Post
Polls tables relationships nabeelalkaff BOOK: ASP.NET Website Programming Problem-Design-Solution 2 August 10th, 2006 12:18 AM
Could not delete from specified tables. (Er 3086) Romunda M. Harris-Fonvill Access 0 April 12th, 2006 11:31 AM
How do i delete relationships programmatically Ssemat VB.NET 2002/2003 Basics 0 December 23rd, 2005 09:09 AM
Multiple tables with relationships Please Help Lesviper Access VBA 6 February 3rd, 2005 08:20 PM
Delete from 3 tables collie SQL Server 2000 1 March 24th, 2004 11:49 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.