Solved

Normalising stock control table

Posted on 2004-04-15
10
1,499 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
10 Comments
 
LVL 46

Expert Comment

by:tbsgadi
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!

Gary
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 150 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



0
 
LVL 16

Expert Comment

by:Nestorio
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.

Greetings.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 16

Assisted Solution

by:Nestorio
Nestorio earned 50 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.

Regards
0
 
LVL 32

Expert Comment

by:jadedata
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...
0
 

Author Comment

by:sharfaa
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
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 150 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,
0
 
LVL 46

Assisted Solution

by:tbsgadi
tbsgadi earned 100 total points
ID: 10840952
(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
ID: 10846105
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
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.
0

Featured Post

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

724 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