Table advice

[primagic]

I am looking for advice. I am developing a database for direct selling traders. What I have at the moment is a products table with all the products they can buy from the supplier. This list of products is the same for every trader. It has the buy cost for the trader and the selling cost that they trader should be selling it at.

What I am trying to do is, when they buy a product from the supplier, this gets added to their own individual stock levels. So if Trader A buys 5 apples, their stock level for apples will be 5. Trader B could buy 2 apples,their stock level for apples will be 2. And it wont affect each other.

When they record a sale of a product. ie. If Trader A sells 2 apples then their stock level for apples becomes 3. If they buy another 2 from the supplier, then its back to 5. Also, if their stock level for a product is 0, they can still sell the product but it goes into a negative amount, which them becomes a 'stock to buy list'.

I am struggling to design the tables so I can record individual trader stock levels. There could be anywhere up to 200 traders on the system eventually once they register.

Help please!

 

[PWise]

Well you need for starters
1) A Products table
Productid ,productname ect
2) traders table
tradeerid ,tradername,ect
3)salestable
saleid ,Productid ,tradeerid ,quantity

 

[primagic]

Sorry I should have mentioned, yes I already have a seperate traders table and a sales table.

Where would I control stock levels though? And how?

 

[PWise]

Stock levels have to be controled with the front end

a trader enter a purchas or a sale

a purchas is entered with a postive quantity
a sale is entered with a negtive quantity

Select Productid ,Traderid ,Sum(quantity)
from salestable
Group by Productid ,Traderid

 

[RiverGuy]

Take PWise's example and then also add a Purchase table. Purchases minus Sales = Quantity On Hand.

 

[RiverGuy]

I wouldn't use the same table for sales and purchases. I imagine your real tables have more than four columns. If you combine purchases and sales, you run the risk of some columns relating to only purchases or only sales. You'll end up with a bunch of NULLs serving no purpose.

 

[PWise]

Well if you go with river guy's advice the way to calculat

'stock to buy list'

will be
Select Productid ,Traderid ,Sum(quantity)
From (
Select Productid ,Traderid ,-quantity
from salestable
union
Select Productid ,Traderid ,quantity
from purchases
)Dt
Group by Productid ,Traderid

 

[primagic]

Ok I will try to play around with it. I am using asp.net as a frontend.

Would the user need to enter in a negative amount in a new sale or can I automatically do that in sql

 

[primagic]

I have been trying many solutions to try to get the information I am after but still having troubles.

My end result ideally will be: Kind of like a crosstab I guess

Product Ordered Sold Stock Remaining
Trader A 1 5 3 2
Trader B 1 4 1 3

I have two queries which sum up what each trader has bought and sold. I am have trouble merging the two together.

the queries are (For orders)

SELECT     dbo.Orders.TraderID, dbo.OrderDetails.Product, SUM(dbo.OrderDetails.Quantity) AS TotalOrdered
FROM         dbo.OrderDetails INNER JOIN
                      dbo.Orders ON dbo.OrderDetails.OrderID = dbo.Orders.OrderID
GROUP BY dbo.Orders.TraderID, dbo.OrderDetails.Product

And for sales

SELECT     dbo.Sales.TraderID, dbo.SalesDetails.ProductID, SUM(dbo.SalesDetails.Quantity) AS TotalSold
FROM         dbo.Sales LEFT OUTER JOIN
                      dbo.SalesDetails ON dbo.Sales.SalesID = dbo.SalesDetails.SalesID
GROUP BY dbo.Sales.TraderID, dbo.SalesDetails.ProductID