Designing stock DB

[lico]

hi everyone,
I'm building a database for stock management, all the incoming supply that I get from every supplier goes into one table, and of course the sales I make goes to other table.
Say I'm selling a products package that includes some products, so for that I create a table which includes the packages name; let's say I'll call this one tbl_packages.
I also created another table that includes the content of every package, called tbl_packages_content; these tables are linked by packages ID from tbl_packages table.
These packages contain default products, but let's say I want to have the option to combine some products different then the other packages and I don’t want to do it by adding a new package to the tbl_packages table since I'll use it only once.
As I said before the goal is stock managing, this means that I want to be able to subtract the ordered products from in stock products, in order to know my stock condition.
My question is what will be the best way to build it, since I don't have experience in database building.
Here's my idea but I'm really not sure:
tbl_packages - contains the name of packages.
tbl_packages_content - contains the products within each package, linked to tbl_packages.
tbl_client_order_details – contains the sum of ordered packages for each order.
The aforementioned tables are in charge of the default packages, for the costume packages I'll create other table, called tbl_costume_packages. The information in this table will refer to the products only without categories them as any type of package.
After that I'll create a query which will sum all the products from tbl_client_order_details, this query will also find the sum of the products from tbl_costume_packages, and then I'll subtract this amount from the in stock table.
As I mentioned before I'm new to DB designing, so if anyone can help me to see things brighter in order to create the right design I'll appreciate it.
thank's​

 

[lico]

hello again,
thank you for your response, i read this article and got more accurate picture.
still, my situation is a little different so i wasn't able to get the answer from it.

thanks any way

 

[gemma-the-husky]

personally, i would try to have single transactions table, keyed by a transaction type

this avoids having union queries which are pain


goods inwards have a grn record, linked to the stock transaction table (movements positive)

goods outwards have a despatch record, linked to the stock transaction table (movements negative)

the total stock movement is the sum of all relevant transactions

you can then add other transaction types (eg stock take adjustments) to the stock transaction table

FAR FAR easier if you can do it this way - you only have 1 table, and a query based on it is updateable - if you have union queries, they wont be updateable, and it gets harder

--------
is your productpackage just a despatch record, is it a parts explosion - ie you sell a product called a costume package, but it actually consists of a suit jacket, suit trousers, dress shirt etc, - all of which are held separately in your dbs

parts explosions can get tricky, as you have to consider how you deal with a part filled order, where some can be supplied now, and some has to wait for fresh stocks to be received. its stuff like this that makes stock systems so complex

 

[lico]

gemma-the-husky

AWF VIP
hi Gemma,
thanks a lot for your response.

 

[Simon_MT]

I would consider treating your stock in an unpackaged state to begin with. With Chemicals it is impossible to undo a recipe so the combined ingredients are one stock item however it possible to make up a mixture on the fly from the base ingredients.

In essence with the packages all you want to happen is for the stock content to be dropped into Order Details. This way you can remove some items if necessary or complete the whole order manually.

The packages seem to be an aide more than anything else and not really a stock entity.

Simon

 

[lico]

Hi Simon,
Thanks for your response,

I think you guys didn't get my question because of my bad English, or i didn't get your answer. I'll try to rephrase and be clearer this time.

First here is my goal:
Creating an inventory DB.

What do i need?
I sell some products; these products are always combined together and sold as a package.
I have my default packages but i also sell costume packages according to client needs.

Here's what i have done so far:

tbl_client_list - contain client information
tbl_client_order - contain general orders information but not the products, of each client and joind to tbl_client_list by client_ID filed.
tbl_packges - contains names of packages i sell.
tbl_products - contain names of products i sell.
tbl_packges_content - contain informatrion about the products included in every type of package.
This table is joined to tbl_packages by package_name field as a lookup field and to tbl_products by product_name also as a lookup field,
i also created a quantity field for each included product since i might include a few pieces of the same product in one package.

tbl_client_order_details -
Here's my problem, i created this table so it'll contain information about the packages ordered and the quantity of each ordered pack.
For calculating the outgoing goods i created a query that multiply the quantity field of each ordered pack by the quantity field from tbl_packges_content.

Till here everything went fine,
My problem is with costume packages,
Since these are costumed and are not usually sold as a regular product,
I don't want to include them as a regular pacakge by adding them to tbl_packges and tbl_packges_content.
Yet i want to be able to calculate the outgoing goods included in these packages for my stock condition.

The way i see it, i have two options:
1) Adding each costume package to tbl_packges and tbl_packges_content so the query will calculate it with the rest.
2) Design the DB as individual products from the beginning.

The reason I'm trying to find a way is because this application will go to the user when it's done and i want to simplify things.
Let's picture a situation when a client place an order, the user feeds the information to the DB, now if the client ordered some regular package
Everything it's ok but let's say the client want some costume package i don't want the user to create a new package but to be able to create it on the fly as a onetime product.
i know that it must be stored somewhere but i prefer not to store it as a regular package since i don't want it to appear as one of the options in the packages type.

So what will be the right way? Creating a new table for costume products?

I hope this time I phrased my question a little clearer.

thank you very much​

 

[Simon_MT]

Another way of looking at the problem is that Products include all Packages and all single Products. Product Details contains all constitutants that make each Product line. For Packages this could be many whilst single Products would only contain one record.

The advantage is that you sell out of Products irrespective of what sort of Product it is and use the Product Details for Stock control.

Simon

 

[gemma-the-husky]

i dont think you should try to deal with "custom packages" as anything special - any stock/inventory should go through your standard system.

maybe just have a falg on the package to identify it as non standard - or an order number link to tag it to a specific order, so it os not generally available

I said dealing with parts explosions was tricky - you are selling item1, but it really consists of several other products, some of which may be out of stock. That concept is qite complex to program and manage