August 13, 2010 at 12:27 am
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
August 13, 2010 at 12:32 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 16, 2010 at 6:51 pm
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
August 16, 2010 at 11:19 pm
Why would you put the column Date into the index?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 17, 2010 at 9:17 pm
Makes sense, Date need not be required to be part of index for the problem I posted.
Appreciate your help!
Thank You,
Mandar
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply