Reduce the number of queries

database-design

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.

Related Question