Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to optimize Inventory database transaction Expand / Collapse
Author
Message
Posted Friday, August 13, 2010 12:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 17, 2010 9:11 PM
Points: 3, Visits: 8
Recently I have been to interview where I got a question regarding how to maintain an inventory and get some reports and optimize transaction.

Scenario:
Multiple vending machines in facility each has cold-beverages (Coke, fenta, soda etc). All the vending machines are refilled every week-end to its maximum capacity and automatically inventory is updated via vending machine interface. Individual employee can get the beverage from vending machine by swaping access card provided by company .. End of every month (or anytime) a report needs to execute to find out how much beverages taken by employee from which department and total cost for employee and department.

For this scenario I have come up with following database design (correct me if I am wrong)

Location (Table)
-LocationID (PK)
-LocationName


Department (Table)
-DepartmentID (PK)
-DepartmentName
-LocationID (FK)
etc..

Employee (Table)
-EmployeeID (PK)
-EmployeeName
-DepartmentID (FK)
etc...

VendingMachine (Table)
-VendingMachineID (PK)
-VendingMachineName
-LocationID (FK)
etc..

Pricing(Table)
- PricingID
- Cost
- CostDate

Product (Table)
-ProductID (PK)
-ProductName
etc..

Inventory (Table) ---- The great table
-InventoryID (PK)
-InventoryDate
-ProductID
-Quantity
-VendingMachineID
-PriceID
-EmployeeID
-InOut - (IN-1/OUT-0) IN-Inventory added; OUT-product sold


The task is to write a simple sql to get following information
- Before any OUT transaction make sure there are enough product in vending Machine.
- generate a report displaying Employees who had took product from vending machine by department (not discussed here)

The SQL I wrote was
Declare @askingQuantity int
Declare @current int
Declare @out int
SET @askingQuantity = 1 -- Say default entry


Select @current = Sum(Quantity)
FROM Inventory Where productID = 12 -- just some number
AND INOUT = 1 AND VendingMachineID=2

Select @out = Sum(Quantity)
FROM Inventory Where productID = 12 -- just some number
AND INOUT=0 AND VendingMachineID=2

IF (@current < (@out+@askingQuantity)) THEN
RETURN FALSE
ELSE
RETURN TRUE.



Now the immidiate second question after this SQL was .. You will search all the table everytime to find out if the OUT transaction is possible? Can you optimize it.

Could you help me on this one .. as I am trying to find out a reasonable solution. It does make sense every time there is a OUT transaction you will search all Inventory table in order to find out that machine has enough product for dispensing.


Thanks in advance,

Mandar





Post #968673
Posted Friday, August 13, 2010 12:32 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:00 AM
Points: 42,774, Visits: 35,871
If you add an appropriate index, you won't be searching the entire table.

I suspect this is one of the interview questions where there's no right or wrong. They want to see if you can explain your design, the good, the bad, if you can see flaws, if you can see alternatives, if you can explain why you designed things in a specific way.

I used to like asking these kind of questions. Lets you see how a candidate thinks, whether they're confident in their work or whether they'll defer immediately to the interviewer, etc.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #968674
Posted Monday, August 16, 2010 6:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 17, 2010 9:11 PM
Points: 3, Visits: 8
Thank You for prompt response Gail Shaw.

Me being considered newbe for the databases.
By adding composite index on productID, Date, VendingMachineID would do the trick or I am missing point here.

Appreciate your help!

Thank You,

Mandar
Post #970095
Posted Monday, August 16, 2010 11:19 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:00 AM
Points: 42,774, Visits: 35,871
Why would you put the column Date into the index?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #970145
Posted Tuesday, August 17, 2010 9:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 17, 2010 9:11 PM
Points: 3, Visits: 8
Makes sense, Date need not be required to be part of index for the problem I posted.

Appreciate your help!

Thank You,

Mandar
Post #970853
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse