Stock Control in SQL Products Table

Question

Stock Control in SQL Products Table

bkirk

SSCrazy

Points: 2824
More actions
November 22, 2009 at 3:36 pm

#216985

My website uses a shopping cart system for all my online product sales with integrated Paypal processing.
I want to get my stock levels displayed online and keep them automatically updated in my backend SQL Products table. I don't want to go to specific for counting (un)/committed stock, backorders, etc...
I just want to keep it simple in reducing the stock count when sales of units are made through the checkout process and also maintain the addition of stock through the Admin section by manually adding new quantities.
I guess i can get more complex when i get it processing correctly.
At the moment i have been toying with Triggers on my OrderDetails table to update the matching ProductID in the Products table when a new line item is added.
Can anyone tell me how they control their stock level in such a system as online ordering and backend maintenance?
Or perhaps point to some good stock control articles that are specific to SQL tables, trigger, stored procs, or any other best practice?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

don't know how others do it, but I've used an updatable view. the view is basically the main products table, which is joined to a group by/subquery of the "ProductReceived" Table, which might have productid 1 receiving 48 new units over the course of 4 weeks.

that is joined to a subquery/groupby of the "Invoices" table; that query has productid and the count() of the productid.

i repeat the same group by sub queries for info like Shipped and Damaged.

the columns, since it is a view, are always correct when you query them, and the first portion of the table is still updatable for the Products table.

i hate updating a table to try to keep the counts, as eventually, the numbers get out of whack, and I've felt this is the best solution.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

bkirk SSCrazy Points: 2824 More actions · Answer 2

So if i understand this correctly...

Are you saying that your Products table does not actually need a column for Stock Count?

But instead you use the ProductID to link another SQL Table called Stock Received? And this table keeps a FULL history all stock ever received by date?

So to calculate the current Stock on Hand on any given day you would sum the total of:

1) Total of ALL stock ever received in the Stock Received Table

LESS

2) Total of ALL Units of Stock fulfilled on the OrderDetails table (based on status of what was actually shipped/paid)

Am i on the right track here?

Lowell SSC Guru Points: 323518 More actions · Answer 3

bkirk (11/22/2009)
So if i understand this correctly...
Are you saying that your Products table does not actually need a column for Stock Count?

yes that is exactly correct. the Products table only has descriptions and attributes of the product.

But instead you use the ProductID to link another SQL Table called Stock Received? And this table keeps a FULL history all stock ever received by date?

yes, every case of product that is received is in the separate table, with a simple FK to the Products table.

So to calculate the current Stock on Hand on any given day you would sum the total of:
1) Total of ALL stock ever received in the Stock Received Table
LESS
2) Total of ALL Units of Stock fulfilled on the OrderDetails table (based on status of what was actually shipped/paid)

yep it's just a simple calculated field in the view for the current total, derived from the sum of 3 tables...received - (sold +damaged/removed from stock)

Am i on the right track here?

that's the simplified version of it; we could prototype an example schema pretty quick, but it's simple enough; do you see the simplicity of it by using views instead of manually tracking it?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

LutzM SSC Guru Points: 107049 More actions · Answer 4

We do it pretty much the same with just a little difference:

Instead of having three tables like Lowells solution has, we're using one table with positive values for received / reworked a.s.o. and negative values for sold/damaged/removed including handling of results from inventory checks. We do have a column indicating the reason for stock change.

it's just a simple calculated field in the view for the current total, derived from the sum of 3 tables...received - (sold +damaged/removed from stock)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

bkirk SSCrazy Points: 2824 More actions · Answer 5

Both excellent suggestions and i will approach the same.

Perhaps the single table stock control would suit me as i'm not dealing in vast amounts of products or quantities and i like the idea of classification of stock types such as received, damaged, etc...

Thanks.