Deleting stale data from mapper table

Question

I am working on a design with the following entities with C# and Microsoft sql server for my school project

User table - User table that stores user information

Product table - Product table that stores Product information

Favorites table - User product favorites table that maps userId and productId for favorites

User favorites a product PUT - /users/{userId}/products/{productId}/favorites

Checks if userId and productId exists and inserts an entry in the Favorites table with userId and productId

User unfavorites a product DELETE - /users/{userId}/products/{productId}/favorites

Checks if userId and productId exists and removes the existing entry(userId,productId) from the Favorites table

List all favorite products for user GET - /users/{userId}/favorites - Would return a list of productIds

I'm thinking of ways of handling the following use case for List all favorite products for a user

1) If a product is deleted, what are the ways I can cleanup the data from the favorites table.

I'm thinking of the following ways

Delete the entries(userId,productId) from the favorites table as well when a product is deleted.
During the GET call for get all favorite products for a user, I loop through each productId to check if the product exists

Which of these 2 approaches are better or is there anything Im missing here

score 0 · Answer 1 · answered Apr 17 '19 at 14:01

You should use foreign keys to link the product_id in the favorites table to the id of the product in the products table. This way you could define what to do to the related favourites when you delete a specific product.

Since you didn't provided your database schema, the code might need some tweaking based on your column definitions.

ALTER TABLE db.Favorites
ADD CONSTRAINT FK_Favorites_Products
    FOREIGN KEY (ProductID) REFERENCES db.Products(ID) ON DELETE CASCADE

With that query you define a foreign key (called FK_Favorites_Products) on the Favorites table that maps ProductID column to the IDs of Products. This would also prevent to have records in Favorites table with a ProductID that doesn't match any ID the Products table.

Whenever a product is deleted, all the Favorites records that has ProductID = ID of the product you are deleting, will be deleted as well.

Deleting stale data from mapper table

1 Answers1