Improve company productivity with a Business Account.Sign Up


Normalising stock control table

Posted on 2004-04-15
Medium Priority
Last Modified: 2008-03-03
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:-
2. a DEPARTMENT contains many LOCATIONS - A LOCATION should have a least one DEPARTMENT
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)

DeptId            DeptName         ResponsibleManager
001            Sewing         Smith
002            Finishing         William

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            

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  

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
Question by:sharfaa
  • 2
  • 2
  • 2
  • +2
LVL 46

Expert Comment

ID: 10832667
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!

LVL 65

Assisted Solution

rockiroads earned 450 total points
ID: 10833463
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

LVL 16

Expert Comment

ID: 10833614
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.

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

LVL 16

Assisted Solution

Nestorio earned 150 total points
ID: 10833715
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.

LVL 32

Expert Comment

ID: 10835129
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...

Author Comment

ID: 10835399
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
LVL 65

Assisted Solution

rockiroads earned 450 total points
ID: 10840387
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,
LVL 46

Assisted Solution

tbsgadi earned 300 total points
ID: 10840952

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

Author Comment

ID: 10846105
My questions was very simple-Is my table normalised? If not, how should it look like when normalised?
LVL 32

Accepted Solution

jadedata earned 600 total points
ID: 10846540
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.

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

579 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