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

Question

The following is a simplified version of my database structure (creating 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 that come in different sizes . [Users] is the standard asp.net membership repository.

I would like users to be able to “love” and appreciate certain elements (and sizes), so my initial impulse was to implement a couple of base mapping tables:

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

However, if I use referential integrity, I will of course run into a problem when the store owner wants to delete the item, the size of the item or even close his / her entire store.

The parameters that I have identified:

• Cascading Deletes . The main transition the next time the user logs in, his favorite items are completely missing.
• Soft / Logical Deletes . I shy away from them in this case, because when I used them in the past, adding WHERE IsActive to each query, cumbersome with table joins, Plus I believe (correct me if I'm wrong) this adds some complexity to EF4, let's say Items.Includes("ItemSizes") .
• Do not apply referential integrity (only for [FavouriteSizes].SizeID FK and [ItemRatings].ItemID FK) . I have never done this before. This sounds like the “easiest” answer, but I'm not sure if he will come back to bite me later.

Given that not following these two foreign key constraints looks like the easiest option, my implementation will be as follows:

• Add ItemName to [FavouriteSizes] and fill it with ItemSize.Item.ItemName when user users have size
• Add an auxiliary item to display a notification if the selected item is no longer available ( FavouritedSize.Items Is Nothing ) so that users can remove this item from the favorites list.
• Make sure a report of the "Best line items" type only discards items that still exist.

Is this implementation causing problems in the future? Is there a good reason why I should go towards implementing soft deletions instead of just not using referential integrity (other than saving historical data for reporting)? I do not have the option that works best?

Answer 1

Non-compliance with referential integrity is a risky thing if you are absolutely and completely not sure that ANYONE, BUT YOUR application will never forget the data in this table (and that your application is, of course, checked to ensure that it maintains integrity)

In a practical scenario, I believe that this approach is risky, because as soon as the system works in real time, there is always a chance that other applications will appear, especially tools and patches for data migration / some direct data manipulation in some urgent scenarios - who ultimately manipulate the data and in the absence of restriction, they will not be able to identify the relationship and could potentially put the wrong data.

Also, I don’t know if you need this input, but looking at your circuit, I will probably consider a small 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 divided the table [ItemSizes] into [Sizes] and [ItemSizes] .

Thus, you can love both the element and the size (as you are doing now), or even an element of a certain 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 Favorites and Rating tables - in addition to your main tables - using WHERE IsActive checks and makes favorites / ratings soft - are deleted when an item / item size / size is deleted, and then the presence additional logic when displaying your favorites / ratings, to indicate the absence of previously added ratings / favorites for the user, it seems to me the best option.

I'm not sure how IsActive validation works with the EF-havent used by EF, but in general I would say that verifying that validation is always present in all requests is easy, ensuring that a specific point is validated - as part of the process review. Usually this becomes the second nature of the team and an extra effort to ensure that the verification is negligible.