sharfaa
asked on
Normalising stock control table
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
Greetings.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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...
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...
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
My questions was very simple-Is my table normalised? If not, how should it look like when normalised?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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