SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to optimize Inventory database transaction


How to optimize Inventory database transaction

Author
Message
mandar109
mandar109
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214974 Visits: 46269
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

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


mandar109
mandar109
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214974 Visits: 46269
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

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


mandar109
mandar109
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search