Soft/Logical Deletes vs No Referential Integrity vs ...?

Question

Below is a simplified version of my database structure (building a proof of concept site in MVC 2 with Entity Framework 4 as my ORM):

[Stores]
StoreID (PK)
StoreName

[Items]  
ItemID (PK)  
ItemName
Description
StoreID (FK)

[ItemSizes]
SizeID (PK)  
SizeName
Price
ItemID (FK)

[Users]
UserID (PK)
UserName

Stores sell items, which come in varying sizes. [Users] represents the standard asp.net membership store.

I'd like to implement users being able to "favourite" and rate particular Items (and Sizes) so my initial impulse was to implement a couple of basic mapping tables:

[FavouriteSizes]
UserID (PK) (FK)
SizeID (PK) (FK)

[ItemRatings]
UserID (PK) (FK)
ItemID (PK) (FK)
Rating

However, if I enforce referential integrity, I will of course run into an issue when a store owner wants to delete an item, item size, or even shut down his/her whole store.

The options I've identified are:

• Cascading Deletes: The major con being the next time a user logs in, his favourited items are missing entirely
• Soft/Logical Deletes: I'm shying away from them in this case because when I've used them in the past, having to add WHERE IsActive to every query gets cumbersome with table joins. Plus I believe (correct me if I'm wrong) this adds some complexity in EF4 with, say, Items.Includes("ItemSizes").
• Not Enforcing Referential Integrity (on the [FavouriteSizes].SizeID FK and [ItemRatings].ItemID FK only): I've never actually done this before. It seems like the "easiest" answer, but I'm not sure if it will come back to bite me later.

Given that not enforcing those 2 foreign key constraints seems like the simplest option, my implementation would be:

1. Add ItemName to [FavouriteSizes], and populate it with ItemSize.Item.ItemName when a user favourites a size
2. Add a helper to display a notification if a favourited item is no longer available (FavouritedSize.Items Is Nothing) so users can remove that item from their favourites list.
3. Ensure that any "Top Rated Items" type reporting only pulls back items that still exist.

Is this implementation going to cause problems down the road? Is there a strong enough reason why I should go to the trouble of implementing soft deletes instead of just not enforcing referential integrity (other than preserving historical data for reporting)? Am I missing an option that would fit better?

Answer 1

Not enforcing the Referential Integrity is a risky thing to do unless you are absolutely and completely sure that NO ONE BUT YOUR application is going to ever populate data in this table (and that your application is of course tested to ensure it preserves the integrity)

In a practical scenario, i have found this approach to be risky because once a system is live in production, there is always a possibility that there will be other applications coming up especially data migration tools / patches / some direct data manipulation in some urgent scenarios - which end up manipulating the data and in the absence of a constraint, they will have no way to identify the relationship and could potentially end up putting in data that is incorrect.

Additionally, i dont know if you need this input, but looking at your schema, i would probably consider a slight change

[Stores]
StoreID (PK)
StoreName

[Items]  
ItemID (PK)  
ItemName
Description
StoreID (FK)

[Sizes]
SizeID (PK)  
SizeName

[ItemSizes]
ItemID (PK)
SizeID (PK)  
Price

[Users]
UserID (PK)
UserName

Note: I have split your [ItemSizes] table into [Sizes] and [ItemSizes].

This way, you can favourite, either an Item or a Size (as you are currently doing) or even an item of a specific size.

[FavouriteSizes]
UserID (PK) (FK)
SizeID (PK) (FK)
IsActive

[FavouriteItemSizes]
UserID (PK) (FK)
ItemID (PK) (FK)
SizeID (PK) (FK)
IsActive

[ItemRatings]
UserID (PK) (FK)
ItemID (PK) (FK)
Rating
IsActive

To summarize, Adding IsActive fields, even to your Favourite and Rating tables - in addition to your master tables - using WHERE IsActive check and making favourites / ratings are soft - deleted on removal of the item / item size / size and then having the additional logic on display of your favourites / ratings to indicate the non - existence of earlier added ratings / favourites to the user, seems to me the better option.

I am not specifically sure of how the IsActive check works with EF - havent used EF - but in general, i would say that making sure that check is always present in all queries is done easily by ensuring that specific point is checked - as part of the review process. Usually, it becomes 2nd nature within a team and the additional effort to ensure that check is negligible.