I have a website where I want to create option for user to remove items from their bag. I am doing this task using many different queries.
Is there way to reduce the number of queries?
I have the following 3 tables:
- BAG_TB
- BAG_ITEM_TB
- ACCOUNT_TB
Here is how I am doing it:
1- Get the bag_ID
of user who is using the site. This is by account_id
.
select Bag_ID
FROM [BAG_TB] b
WHERE Account_ID = @Account_ID;
2- Based on above step, it checks how many records are on BAG_ITEM_TB
. This will tell me how many items are in current user bag.
Select Bag_ID
FROM [BAG_ITEM_TB]
WHERE [Bag_ID] = @Bag_ID;
3a- If there is 1 record returned from step #2, this means I have to delete bag. So do a delete on BAG_ITEM
, BAG_TB
, and ACCOUNT_TB
.
3b- If there is more than 1 record returned from step #2, then only delete from BAG_ITEM_TB
because there is more than 1 item in bag… so do not delete from BAG_TB
and ACCOUNT_TB
Below one example of my delete queries:
delete FROM [BAG_ITEM_TB]
WHERE [Bag_ID] = @Bag_ID
AND [Product_ID] = @Prduct_ID;
delete FROM [BAG_TB]
WHERE [Bag_ID] = [Bag_ID];
delete from [IS_ACCOUNT_TB]
WHERE Account_ID = @Account_ID;
Best Answer
Define referential integrity in your database, so that a bag cannot be removed so long an item exists, idem for the accounts and bags. Then don't do the selects but provide exception handling on the deletes. Your database will do the selects itself, to enforce the integrity.