By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,501 Members | 2,797 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,501 IT Pros & Developers. It's quick & easy.

Stock Management Database

P: 2
I am working on creating a stock management database for a small company.

The company receives the merchandise from a handful of suppliers and stocks it before it is sent to the customers – retail outlets.

The company works on a concession basis – i.e. the customers do not pay for the delivered goods but sell it on and then take a percentage of the sales. The goods delivered to the retailers can be considered to be “in stock” at the retailer.

The retailers report back what they have sold on a weekly basis and take a commission on this

Now to get a better understanding of what is in stock in house and at the retailers and to get an understanding of what is selling and what is not they would like a database.

I would like some help to see if I am working in the right direction.

Are the tables mentioned below enough to create a good working database or do I need to include more?

The stock management database is described below in its various tables:
tblSupplierTable
SupplierID (PK)
SupplierCompanyName
SupplierFirstName
SupplierLastName
SupplierAddress
SupplierAddress2
SupplierAddress3
SupplierCity
SupplierCounty
SupplierPostCode
SupplierCompanyWebsite
SupplierPhoneNumber
SupplierFaxNumber
SupplierMobile
SupplierEmail

tblStockIn
StockInID (PK)
SupplierID
ProductCode
PurchaseQty
PurchasePrice
RRP (recommended retail price)
PurchaseDate

tblProductList
ProductCode (PK)
ProductDescription
SupplierID
SupplierName

tblRetailOutlets
CustomerID (PK)
CustomerCompanyName
CustomerDeliveryAddress
CustomerDeliveryAddress2
CustomerDeliveryAddress3
CustomerDeliveryCity
CustomerDeliveryCounty
CustomerDeliveryPostCode
CustomerCompanyWebsite
CustomerPhoneNumber
CustomerFaxNumber
CustomerMobile
CustomerEmail

tblGoodsOut
DeliveryID (PK)
CustomerID
ProductCode
DeliveryQty
DeliveryPrice
DeliveryDate

tblSales
SalesID (PK)
CustomerID
ProductCode
SalesQty
SalesPrice
SalesDate

tblStockAdjustment
StockAdjID (PK)
ProductCode
AdjPrice
AdjDate

The reasonings behind the tables are:
Suppliers – The handful of suppliers and the key persons dealt with are managed in this table.

Stock In – when new stock is required the suppliers are contacted and new supplies are ordered. When the stock arrives only the received goods is typed into “Stock In” (this is to avoid the problems of goods that have been ordered but not supplied – when this happens it will not be typed in the “Stock In” and when the missing goods arrives a new “Stock In” will be created).

Product List – Most products are repeat so to make the entering of “stock In” as easy as possible a drop down menu will appear in a form used for entering the “stock In”. When a new product line arrives it will have to be typed into the “Product List” before it can appear in “Stock In”.

The three above mentioned tables provide the information about the incoming goods and goods in stock “in house”.

Customers – the different retailers the company is working with appear as “customers” and all the information needed to ship the goods to them appear in this table.

Goods Out – When goods are sent to the retailers the data is entered into a “Goods Out” form and the accumulated “Goods Out” forms for each retailer will give information on the goods in stock at the retailer (minus the sold goods described next). It is my plan to use the “Goods Out” as the delivery note for the customer and at the bottom of this note it should show the amount and value of goods in stock before the current delivery, the amount and value of goods of the current delivery and the total accumulated amount and value of the goods in stock.

As some retailers are already “live” before this system is in place I will ask the company to do a stock count at the retailers and then enter the data into my stock management database as “stock in” and “stock out” to get all the current retailers’ stock into the system…

Sales – Will give information about what the individual retailer is selling, which is used for restocking and current stock level at the retailer.

Stock Adjustment – The adjustment table is meant to deal with goods that are sold at discounted prices or goods sent back because it is damaged. When future stock counts are made at the retailers the adjustment table will be used to adjust the difference between the database stock count and the actual stock count.
May 18 '07 #1
Share this Question
Share on Google+
1 Reply


P: 49
Hi asmusdk

Very quickly and I do mean quickly looking at your planned Db I would advise you record quantities in your depot then youy can "Transfer it" to the retailer who will of course sell That way you will have YOUR audit of stock in the warehouse , retail, sold scenario. If you have a buyer for a certain product group they could see a snap shot of the products especially fast moving which are requiring a long lead in to delivey to yourself. It also makes stock rotation easier.
Plus you may have to deal with large shipments incoming as a purchase is at a bargin price, knowing exactly what you have will tell you if you have space for it, if its a seasonal and needs to outed t the retailer for that "BOGOFF" option to clear the stock etc and that will also allow monitoring those seasonal as well


The other point Id make is ensure you record the stock adjustment reason i.e. Damaged, over delivery, Reduced to clear

If you are selling goods who take a %age as cost then it is veryu easy for the dishonest one to keep on requesting reductions due to shorrt delivery, or damaged but saleable at RTC price when in fact they are bumping up their profit line. Use a trend analiysis.


as I said , just a qucik initial look at the format

gareth
May 18 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.