Solved

Normalising stock control table

Posted on 2004-04-15
10
1,462 Views
Last Modified: 2008-03-03
Hi
I'm designing a stock control database that will manage all accessories within a manufacturing company and do all the necessary work to control the input and output of the store department. Its Main Objective is to allow:
1-      Department’s Request of items
2-      Allow to search availability of items and its location
3-      Update all Stock transaction(issue, return, update stock from supplier,damaged)
4-      Give an alert for non-returned items
5-      Send reminder
6-      Provide Info about Dormant Stock
7-      Ordering Of Item

I have done my ERD diagram with the following entity and relationship:-
1. a DEPARTMENT can REQUEST many ITEMS - ITEMS can be REQUESTED by many DEPARTMENT
2. a DEPARTMENT contains many LOCATIONS - A LOCATION should have a least one DEPARTMENT
3. ITEM are STORED in many LOCATIONS- a LOCATION can STORE many ITEMS
4. ITEM is ORDERED from many VENDOR- A VENDOR can received ORDER for many ITEMS.

Items are spare parts used for machines, attachement guides for machine and the machine itself. The store is responsible to manage these items. Machines are considered to be an item which are returned to the store and reallocated to other department. So the store should be able to locate where are the machines and the attachement guides, whenever there are demands for it. Location can be the store itself or locations from a department. This is because the machines are being stored and used at the same time in a department. So department will request machines, attachement guides from the store, which might found in the store or in locations from the departments. A spare parts also are requested by department which need to be used for the machines

My tables and attributes with sample data are as follows from the ERD  
(NA=Not Applicable)

Table DEPARTMENT
DeptId            DeptName         ResponsibleManager
001            Sewing         Smith
002            Finishing         William

Table LOCATION
LocationId                      DeptId      LocationDescription      LocationType1
L001            001      Store            Shelf05
L002            002      Finishing            Section01
L003            002      Finishing            Section02

Table ITEM
ItemId      Item        Item            Item      Machine  Unit      Status      Reorder      UnitOf
      Descrip        Category     Specif    Code       Price                    Level         Measure              

I001       needle        spareparts    50mm         NA         1.00    Active       5000           unit
I002       kansai        machines      NA         M001      5000   Dormant    5                  unit
I003       folder             workaids     1cm         NA         100     Active        0               unit
I004       Oil                 spareparts    L01         NA         10       Active       50              litre
I005       lock        machines      NA              M002      3000   Active       10              unit

Table TRANSACTION(request)
Trans      Dept    Item    Trans          Trans      Trans     Approved     ActualUnit    Unitof           Machine
Id            Id        Id        date            Type       Qty         By               Cost            Measure      Code

T001       001       I001      15/02/04     issue       100         Smith              1.00          unit             M002
T002       001       I003      15/03/04     issue        5        Smith              100              unit                 M001
T003       001       I003      18/03/04     return      3        Smith              100           unit              NA
T004       002       I004       20/03/04    issue        2        William            10             litre                 NA

Table STORE(storage)
LocationId                      ItemId      QuantityOnHand      
L001            I001      20,000                              
L001            I002      10
L002            I002       2            
L001            I004      300            

Table ORDER
Order    Item    Vendor  Order         Order               Qty             UnitCost  
Id          Id        Id           Date          Status               Ordered

P001     I001    V001     01/04/04    Inprocess          2000            1.05
P002     I001    V002     03/04/04    Receipt             4000            1.10
P003     I002    V002     05/04/04    Receipt             1                  5000
P004     I001    V002     08/04/04    Return to vendor      200             1.10  

Table VENDOR
VendorId  VendorName ContactPerson VendorCountry Address
V001          Tex                bob                  USA         NewYork
V002          Sparex           steven               UK         Harrogate

From database design books they said that we should normalize your tables. From what I have read I think that my tables are already in third normal form
Please can any expert confirm that I 'm right. If ever the tables are not normalised . Can you show me how to do it in the example that I have given you above? Or do we have to correct my initial design for this stock control example
0
Comment
Question by:sharfaa
  • 2
  • 2
  • 2
  • +2
10 Comments
 
LVL 46

Expert Comment

by:tbsgadi
Comment Utility
I would add some more Tables:
1) Order Status
2) Countries
3) Trans Type

The Quantity On Hand isn't normalised but I'd leave it as is.
Everything else I'd leave as is.
Good Luck!

Gary
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 150 total points
Comment Utility
Generally any lookups, you should add them as a table

Item Category looks repeated in table ITEM
Put them categories in a table

Is it possible for staff manager's to be responsible for multiple depts?
Consider putting that as a separate table
ResponsibleManager in table DEPARTMENT
ApprovedBy in TRANSACTON
both use names


Countries are also a lookup, maybe that should be in it also

Not sure about your trans type and status level, if they are boolean type values (one or the other) and expect to remain that way, then fine leave it as is, but dont store text, store numbers, like 1,2 or single characters
If you expect different types to come in the future but not many, storing numbers still okay, but you can still store in a another table


table ORDER - orderstatus looks repeated, that could go in



0
 
LVL 16

Expert Comment

by:Nestorio
Comment Utility
I think that Code machine, Unit of measure and Unit price are attributes of Item entity so they should not go in the Transactions table.

Greetings.
0
 
LVL 16

Assisted Solution

by:Nestorio
Nestorio earned 50 total points
Comment Utility
Also if "Approved by" is going to be the Department Manager, this is an attribute of Department entity (do not place it into the Transaction table). It would be needed a flag relating the transaction status (approved or not).

Among your premises there is a relation between Departments and Locations, wich is not reflected in your ERD.

Regards
0
 
LVL 32

Expert Comment

by:jadedata
Comment Utility
Use your Transaction table as the source of your Balance reports.  If you endeavor to have a table of balance data, you are no longer in realtime for your balances.  You MUST update this balance table everytime (or periodically at your discretion) update the Balance table to have it be accurate and in sync with the Transaction Table.

Your alternative is good table design and for a balance:
  SELECT ItemId, Sum([Amount]) as BalanceOnHand, Last([UnitofMeasure]) FROM Transactions GROUP BY ItemID WHERE ([ItemID]="A");

you can do this with one or all of the items, and this sql represents the balance at runtime...
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:sharfaa
Comment Utility
Thanks for your comments: Now my questions as per answer
1. tbsgadi
    (a)What are the fields that we need to put in the new tables that you mentioned and their relationships?
    (b)Why is the quantity on hand not normalised?

2. rockiroads
        (a) Do you mean that we need to create a table for item category. But these take only three values either an item   is a machine, a spare part or a work aids. Then what is the purpose to have a table
        (b) A manager will be responsible of only one department. I think the last comment of Nestorio is what you mean.
        (c) Trans type is transaction type, that is it can be an issue to a department, a return to store or a return to vendor concerning an item.
        (d) if order status should go in a table then how it should looks like.

3. Nestario
        (a) Machine code has been included in transaction table because as I have said an item spare parts can be used for a machine. Therefore if we want to know how much spare parts are used for a machine, then we can query a spare parts item per machine code.
        (b) What if you buy an item per metre and issue it  per cm
        (c) We need to record the price for a transaction to be able to keep records for futur queries
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 150 total points
Comment Utility
regarding (a)
  if you feel its fixed then its okay to put in that value, would be better if you use just a number of character to identify this, one thing you do not want is long repeated text -
  Item category can be considered an entity because it is like a list of lookup values, which one or more tables may use
  Probably design issue, depends on deep you want to go down

(d) your order status listed include Inprogress, Receipt, return to vendor, you probably have many more, so like with a, it is a lookup list and so you could put these in a table called order status then store the order status id instead of the order description in the order table,
0
 
LVL 46

Assisted Solution

by:tbsgadi
tbsgadi earned 100 total points
Comment Utility
(a)

1) Order Status :OrderStatusID (Number-Primary Index),OrderStatus (Text)
2) Countries:CountryID (Number-Primary Index),Country(Text)
3) Trans Type:TransTypeID (Number-Primary Index),TransType(Text)


(b) The Quantity On Hand can be calculated (Orders -transaction etc) But this is cumbersome & I would leave this field unnormalised
0
 

Author Comment

by:sharfaa
Comment Utility
My questions was very simple-Is my table normalised? If not, how should it look like when normalised?
0
 
LVL 32

Accepted Solution

by:
jadedata earned 200 total points
Comment Utility
sharfaa:  While your question is simple the answer is not.
  Is it normalized, yes.  Is it fully and completely normalized to the nth extent, no.

  but the good news is that if you consider the design to be consistant with the process model,... your good.

  if the design is
      efficient enough  (data relationships are consistant with process entity relationships)
      compact enough (not a lot of data repeated unnecessarily)
      thorough enought (holds all the data you want it to)

you're in good shape.

Everything else is opinion.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now