[resolved] How to design the tables???

[SaharaWizard]

Hello Guys
I am working on a little inventory application (VB6, ADO, Access2000).
I am trying to design several tables to organize and manipulate data regarding production and raw material (pretty standard stuff for an experienced developer).

I keep track of raw material in a table called tblRaw
I keep production records in tblOrder.

For each production item (single record in tblOrder) user have to look at list of raw materials and pick up 3 to 5 different raw material and uantities to be used.
I have to do the issues:
[B]
1. Keep track of how much of each raw mat. will be left in stock
2. At the time of creating order I have to ALLOCATE the specific quantities, but at the date of production the ALLOCATED quantity should be subtracted from the available amount.
3. if production get cancelled the amount should be added back to the original amount of each raw material.

I have thought of a few different ways of going about it, but I am hoping somebody with more experience be able to tell me what is the most practical way To design the tables.

Thank you in advance.

[szlamany]

I've answer this in other threads recently - but I'll give it another try.

It does not make sense to me, in a SQL world, to have a RAW material file and a Order file.

You should have a single inventory file - something like this (and this is just for example purposes):

Product Id
Transaction Date
Transaction Type (C for CREDIT, D for DEBIT)
Transaction Sequence (if needed)
Reason Code (I for initial quantity, R for restock, O for order, A for adjustment)
Quantity

So you would end up with data like this:

ProdId   TransDate    Type  Seq  Reason  Quantity
000001  2004-02-01    C     001     I       50
000002  2004-02-01    C     001     I      100
000001  2004-02-10    D     001     O       20
000001  2004-02-10    C     002     R      100
000002  2004-02-20    D     001     O       99
000001  2004-03-01    C     001     A        2
000002  2004-03-01    D     001     A        1

Some initial stock of product 000001 and 000002 on 2004-02-01.

Then an Order and Restock of product 000001 on 2004-02-10.

Order of product 000002 on 2004-02-20.

Then some stock adjustments on 2004-03-01 - found out we had 2 more of product 000001 and that the remaining quantity of product 000002 was lost/damaged.

This main INVENTORY table drives everything about the quantity of stock - on-hand, ordered, sold - all figures - date driven.

You have additional tables for order info - linked however you want - maybe with product id, date and sequence - only when you have a REASON code of "O" for order.

This respects the "set-based" logic that you are supposed to use in SQL - and looses the old "record-by-record" processing techniques that we should avoid in SQL.

[Ecniv]

szlamany's post covers the most of what you want to do.

You could have that as the main code, but you'd need another field at the end for cancelled information (so as to ignore that row if its cancelled in the Sql sums).

Other than that I think szlamany's post covers it. I was going to suggest different table layouts, but you would only be able to experiment with them if you get the time and they come back to szlamany's idea in general...

Good luck designing it.

[SaharaWizard]

Hi guys
I think I was not clear in my question:
I have to assign raw material for each production order and keep track of amount of raw material left.
If the production order is at production date amount should be TAKEN, but before that date should show as ALOCATED in the raw material inventory.
For example:
we need to see 200 kg of suger is in stock of which 18 kg is alocated to order # 4 (which goes to production tomorrow). So avaialble is 182 kg.

How would you do that?
Thanks SW

[szlamany]

You just add more columns to the table - for a status field, maybe, that indicates that you have ALLOCATED some quantity for a pending order.

When that ORDER is filled, you put a new row in representing the order, and flag the ALLOCATION row as "dead" - so that you don't double hit the quantity for that product.

That's kind of how ENCUMBRANCES work in a INVOICE/PURCHASE ORDER system.

[SaharaWizard]

Hi Szlamany
I thank you for your suggestions but I truly have no clue as what you mean (and it is me!).
Look at the exact situation:
I have an order for 200 Kg of a product so it should go to "ProductionOrder" tables.
When I create the "ProductionOrder" record I have to pick up 1 to 5 types of raw material at various quantities.
So, each of my Raw material records should go down by the allocated amount and this amount should show on their inventory as Alocated.

Looking at your example I can see no way as how to apply that technique to this specific problem.
Any suggestions?
Reagrds
SW

[szlamany]

You could add some columns - let's say we add a column for QUANTITY ALLOCATED and also one for ORDERNUMBER.

Let's assume that ORDERNUMBER 99991 requires two products to be filled.

On 2004-03-01 the ORDER comes in - we allocated 20 units of our two products. Let's use a new REASON code of P, for PRE-ALLOCATED.

On 2004-03-02 the ORDER is filled - so we actually put in amounts in the QUANTITY column to reduce stock. We also put in "opposite" values in the QUANTITY ALLOCATED column so that the "encumbered" amount we were holding is "freed" up.

Using that method allows you to fill the ORDER for a slightly different amount than what you PRE-ALLOCATED, or to partially fill the ORDER - and either reduce the PRE-ALLOCATED amount back to 0 or to leave some PRE-ALLOCATED.

ProdId   TransDate    Type  Seq  Reason  Quantity  QuantityAllocated  OrderNumber

000001  2004-02-01    C     001     I       50
000002  2004-02-01    C     001     I      100

000001  2004-03-01    D     001     P        0           -20             999991
000002  2004-03-01    D     002     P        0           -20             999991

000001  2004-03-02    D     001     O       20           +20             999991
000002  2004-03-02    D     001     O       20           +20             999991

So the formula for stock on hand is:

SUM(CASE WHEN TYPE='C' THEN QUANTITY ELSE -QUANTITY END + QUANTITYALLOCATED)

[Simply Me]

szlamany, your post would surely help alot...with regards to relating fields..how is it done? I mean i know how to relate but what's the best practice to do such?

BTW: I've been reading a lot post in this section (database development) about using sQL 2005 and it seems a good idea to use it but the thing is, if im using adodb with my msaccess will there be a major overhaul with my code when i implement sql 2005 or sql 2005 express?

I really would like to learn much about database and how to design it so that i can start with the inventory system that im trying to do.

[szlamany]

...with regards to relating fields..how is it done? I mean i know how to relate but what's the best practice to do such?

Please explain you question in a different way - what do you mean by relating fields?

As you can see the table design examples given here provide a way to load up many rows for a single product id.

This is a basic inventory system.

And using SUM/GROUP BY concepts in the SELECT's you can get all kinds of aggregate values.

[Simply Me]

I meant the the relationship as in 1 to many, 1 to 1 etc...

[szlamany]

In post #2 in this thread I discussed not having a RAW and ORDER table but instead having just a single INVENTORY table.

Of course that doesn't mean you don't have a PRODUCT table as well.

That would be where one row is loaded for each product - with ProdId as the primary key - and any fields related to that product - such as ProductName and so on.

This PRODUCT table is in a one-to-many relationship to the INVENTORY table. The INVENTORY table has a row for each "in" and "out" transaction for that PRODUCT. That is described clearly in post #2 as well.

What other tables do you need? What other info do you want to store?

[Simply Me]

im sorry for late replies. Im just using a dial-up connection at its really very slow.... Anyways, im going to attached here the database i have created. I know there's a lot to be corrected with it....while i am thinking of doing it all over again....

I want to store the following: Client Name, Client Allowed Budget, Date of entry, Stocks(qty), Unit, Purchase Price. I want also to know the stock balance last month.

[Simply Me]

ProductID is my PK in my tblStockMasterfile doing so will not allow me to add the same product but of lower or higher purchase price. What should I do with it?

[szlamany]

Please don't attach the database - simply give us the names of the major tables in your database and what the columns in those tables are.

Each of the items you mention:

Client Name, Client Allowed Budget, Date of entry, Stocks(qty), Unit, Purchase Price.

Need to be reviewed as to whether they are static figures (never change) or have the ability to be different in the future. Changing figures need to be in child tables - with proper primary keys to allow them to be unique.

I want also to know the stock balance last month.Please, please, please review the concepts in this thread about storing stock in and stock out values in a transactional inventory table. With that said - you never, never, never store a "balance for last month". That is simply calculated in a select statement.

Select ProdId, Sum(Quantity) "LastMonthBalance"
From SomeTable
Where TransDate<'2007-09-01'
Group by ProdId

This query gives you the accurate quantity on-hand as of the last day of the prior month (assuming we are in Sept 2007).

ProductID is my PK in my tblStockMasterfile doing so will not allow me to add the same product but of lower or higher purchase price. What should I do with it?

The PK of tblStockMasterfile needs to be a compound primary key. Not just ProductId - but ProductId+EffectiveDate.

Those two columns - ProductId and EffectiveDate make up the unique entry for the price - and the EffectiveDate gives you the all important "date" field you need to associate that price with a time frame.

Is this project for commercial purposes or for a school assignment???

[Simply Me]

Sorry if I can't get what you are trying to say I created new database with three tables (just for the time being) namely:

tblClientMasterFile:
ClientCode
ClientName
Budget

tblStockMasterFile:
ProductID(PK)
ProductName
Balance
ReorderPoint

tblStockTransFile:
ProductID
ProductName
PurchasePrice
Quantity
Unit
EntryDate
ReorderPoint

Question: Is my structure correct? The user would enter the product ID and product name in tblstockmasterfile and then user would enter purchase price quantity, unit, entrydate and reorderpoint in tblstocktransfile....

I am really at lost here that's why i attached the database hoping that you would look at it and do some necessary corrections

[szlamany]

Balance should not be a field in any table. The balance is a moving figure - it can always be "calculated-at-query-time" using a SUM/GROUP BY query.

ProductName should not be a field in the tblStockTransFile - as it's already a field in the tblStockMasterFile. Having it be a field in two tables breaks the rules of normalization that should be considered when designing a database. This also seems to be the case for the ReorderPoint - it should only be in the tblStockMasterFile (or you need to explain it's use better to us).

Have you used many JOIN statements in queries before?

tblStockTransFile should have these fields (of the ones you gave in your example):

tblStockTransFile:
ProductID -----\..these two fields make up the primary key - that is
EntryDate -----/ how rows are made unique in this table
PurchasePrice
Quantity
Unit

[Simply Me]

I have edited the tables now with what you mentioned above. Regarding the balance its clear to me that it can be computed using SUM/Grouped by. The balance im referring to here are monthly the stocks on hand per product (I need it so that i can keep track of how many were left at end of each month and add to it the product i will stock-in every first day of the month.). This is why i added that field.

I have tried using JOIN before.

Questions:
1. Since I tblStockTransFile and tblStockMasterFile, it means therefore that when I design my Stock-in GUI I have to provide textboxes for all these fields for data entry right?

2. When saving the entries, my SQL statement would JOIN the tables or just use two separate INSERT for the two tables?

[szlamany]

I simply don't know how to say this any stronger...

You NEVER, EVER store a total in an inventory system. NEVER.

This query I posted:

Select ProdId, Sum(Quantity) "LastMonthBalance"
From SomeTable
Where TransDate<'2007-09-01'
Group by ProdId

Will give you the stock-in for the first day of the month of Sept, 2007. If you think you need to store this figure you are mistaken. SQL is meant to calculate that on the fly.

Realize that the query above is simple - it could be more complex. Given a table that has every month and year as a single row - you can join to that table and gather these statistic for every month since your system went live.

Question#1 - yes - you put textboxes or whatever controls you like on your GUI for all the fields in all the tables that are being maintained.

Question#2 - we do an INSERT into each table as needed - parent table first (if a new product) and then child-tables for stock entries.

[Simply Me]

Question#2 - we do an INSERT into each table as needed - parent table first (if a new product) and then child-tables for stock entries.

How would the SQL INSERT would look like for the case above, would this mean two seaprate entry form?

Since i already have tblStockTransFile for my stock-in, I have created tblStockOut for stocks sold but when i put a relationship with my tblStockTransFile and tblStockOut using the ProductID field, it results to indeterminate relationship. What's needed here?

[szlamany]

From your question I'm assuming you are using BOUND controls on the form - is that right?

And I'm confused about why you still have two tables - one for stock-in and one for stock-out??

[Simply Me]

Im not using bound controls. Im using ADODB to connect to my database. Here is the code in sub main i used to connect to my database

Public Sub openConnection()
  Set oConn = New ADODB.Connection
  
  With oConn
    .Provider = "Microsoft.Jet.Oledb.4.0"
    .CursorLocation = adUseClient
    .Mode = adModeReadWrite
    .ConnectionString = "Data Source=" & DBPath & "\" & DBName & ";Persist Security Info = False;Jet OLEDB:Database Password=xx;"
    .Open
  End With
End Sub

Regarding the separate tables of stock-in and stock-out. I have it so that i can have a record for stocks going out. If this is not a good idea then how should my tables look like and if I use only one tables for stock-in and out which field should I use to put relationship to my client table?

Sorry for my questions, I know this are basic ones which unfornately i still dont know yet and I'm very much willing to learn about it.

Thanks for your patience.

[szlamany]

@simplyme

We have hijacked this thread enough - I've created a new thread with a post about table design - a single inventory transaction table - please let's go to that new thread

http://www.vbforums.com/showthread.p...74#post2994574

Review the post in that thread and give me your opinion (in that thread) of whether this design is valid for your needs.

[Simply Me]

i just edited my database. please refer to the attached file.