Windows 7 32bit 
Access 2007
Hi,
I will warmly welcome the all advises and help to develop this database design in correct way and proper way.
The Process Flow Chart is attached to have a look for better understanding.
Its a manufacturing process flow chart of Pipes Manufacturing Company where I want to focus from Raw Materials Receipt to Finish Goods Transferring to Warehouse.
PROCESS DESCRIPTION
1. Raw Materials Receipt. (From P01 - P02):
The different type of Raw Materials based on Purchase Orders (almost 50 Items) received at "P01" and recorded in ERP system.
1.1 Laboratory Tests & Quality Control.
A sample bag is sent to Lab. For Testing the quality of Materials and a Lab. Technician will Test the Materials as Test Code “T001” based on the Type of Raw Material, there are Specified elements to check for each category of Materials and especially if it not as per standard values, so a report is submitted to Purchase/Stores for rejection of Material.
2. Raw Materials Preparation (From P03 - P05):
The Clay Raw Materials (two types mostly) processed and milled at "P03" through different stages and converted into required particle sizes and then mixed by appropriate percentage as per given "Dry Powder Body Composition" (Which is issued by Lab.) at “P04” and send to next process “P05” to add water into and prepare Mixed Clay.
2.1 Prodn. Data Recording and Data Type:
- @ P03: The Control Room Operators record the kg’s loaded into Hoppers (4 Units) for each type of material.
- @ P04: The Control Room Operators record the kg’s of each type of material mixed according the given compositions of dry powder mix.
- @ P05: The Mixer Operators (2 Units) record the kg’s of each batch (normally fixed quantity like 1,400 kgs) and the quantity of water added to this mix (variable quantity) to maintained the moisture standards of Mixed Clay.
2.2 Laboratory Tests & Quality Control.
- After P03: The Lab. Technician took random samples throughout the shift and performs Test “T002” and advises the concerns.
- @ P04: The Lab. Manager issues Dry Powder Compositions and that is followed at mixing stage for related products until new instructions.
- After P03: The Lab. Technician took samples of each batch and advises the Mixer Operators to maintain the standard limits of moisture.
2.3 Notes:
- Touch Screen Data Entry at “P05” will be the best practice.
3. Product Making and Drying Process ) (From P06 - P07):
The mixed Clay Body is directly sent to the Extruder Machines “P03” (3 Units) where 3 different Size of Pipes as per Monthly Production Plan extruded and loaded on Dryer Cars (as a Carrier to take Pipes trough next Process).
Those loaded dryer cars automatically pushed into the dryers (5 Units) as programmed by Product size. The Dryers are programmed at fixed cycle time (70 Hours and can be changed) and also other parameters like humidity % and Temperature.
3.1 Prodn. Data Recording and Data Type:
- @ P06: The Operator of each machine (3 Units) records the production data as, Dryer Car #, Time, Good Pipes, Bad Pipes and any comments during extrusion.
- The Operators also filled in the Quality check sheet which contains 17 Elements to checked and write down after 3-4 hours Intervals.
- After P06: The Line Controller records the Dryer Car # and the time it pushed into which Dryer #.
3.2 Laboratory Tests & Quality Control.
- After P06: The Lab. Technician took daily random small Pc’s of samples from Pipes from Dryer Cars and performs Test “T04” and advises the concerns.
- After P07: The Lab. Technician took daily random small Pc’s of samples from Pipes from Dryer Cars to measure the moisture of Pipes and performs Test “T05” and advises the concerns.
3.3 Notes:
- The Dryer cars are same in structure and labelled with unique code number (001-400).
- Looking for Touch Screen Data Entry practice.
4. Inspection, Glazing and Loading Process) (From P09 - P12):
The Dryer Cars comes out from the Dryers and shifted to the Production Line ( 2 Units) where Inspectors check the quality and marked as good pipe , reject Pipes.
The Good Pipes lifted by cranes and dipped into the Glaze tank and shift to the flat forms where a Human loader lift the pipes with vacuum loader and set up on Kiln Cars (as Carrier to take the Pipes through the Firing cycle). The reject pipes scrapped in trash tank and send to scrap yard time to time.
4.1 Prodn. Data Recording and Data Type:
- @ P09/P11/P12: The Operator/Inspector of each Production Line (2 Units) records the production data as, Dryer Car #, Time, Good Pipes, Reject Pipes and reason for rejects, Loaded on Kiln Cars #, and any comments Like any Glaze added to the tanks during that specific Car no.
4.2 Laboratory Tests & Quality Control.
- @ P11: The Lab. Technician took daily random samples of Glaze from Tanks and performs Test “T007” and advises the concerns.
4.3 Notes:
- The Kiln Cars are two types in structure and important keep them separate while recording and labelled with unique code number (001-100).
- Looking for Touch Screen Data Entry practice.
- When a dryer car is unloaded and it moves to again Process P06 for loading again. So would be better to know a how many cars waiting for unloading and how main ready for P06 and how many are inside Dryers.
- One Dryer Car will be having less no. of pipes comparing to the Kilns Cars. (Like Product SS200 will be having 21 Pipes on Dryer Car whereas 76 required on Kiln Car.)
- Glaze Supply: The Glaze (Liquid form) is supplied from Glaze section (another process of mixing Raw Materials and prepare Glaze) .
5. Pre-Kiln Dryers and Firing Process (From P13 - P114):
Those loaded Kiln Cars automatically pushed through the Pre-Kiln Dryers (3 Units) and then pushed through the Tunnel Kiln (Firing). The pushing cycle is based on the Firing Cycle (Like Max. 24 Cars per 24 hours and in actual Pushing is 15Cars/24Hrs, Etc).
5.1 Prodn. Data Recording and Data Type:
- @ P14:The Kiln Operator records what time which Kiln Car is pushed inside the Tunnel Kiln.
5.2 Laboratory Tests & Quality Control.
- @ P13:The Lab. Technician took daily random samples from Pipes and checks the moisture of Pipes at this stage and advises the concerns.
5.3 Notes:
- Looking for IPad Touch Screen Data Entry practice so while moving around Operator can enter the time and Kiln Car no.
- Kiln Exit Schedule: what is coming out (which product) from Kiln (Firing) in next 24 hours is very important to schedule the unloading Lines (2 Lines Only specified for products, Like Product-A Can’t be unloaded at Line one due size and weights) to arrange the people and etc.
6. Unloading & Inspection - Sorting (P15):
The Pipes are unloaded from Kiln Cars and inspectors sort out based on quality standards and define in three categories, Good Pipes, Scrapped Pipes (Throw out) and Short Pipes (if full pipe is not good so they marked as GA, GZ, GE categories of short length pipes and processed to "P16" for Cutting into short lengths).
6.1 Prodn. Data Recording and Data Type:
- @ P15: The Inspector filled in the sheet either it is good, reject with reasons (list of reasons 6-7) and short length Pipes with reasons (Same of Reject Reasons).
6.2 Laboratory Tests & Quality Control.
- After P15: The Lab. Technician took daily random samples of Pipes and Performs different Test (4 tests) for different elements.
6.3 Notes:
- Looking for Touch Screen Data Entry practice.
- Very Important Area of Process which shows the results of quality of all the processes starting from P03.
- I am sure it will be possible, if we just click the Kiln Car No and would be able to know its sorting results, from which dryer cars loaded and those dryer cars was produced what time and from body mixed batch and also if there is any Lab test performed on those Pipes or before or after throughout the process.
7. Jointing of full Pipes and Short length Pipes (P17):
At this process, the pipes which marked as good by the Inspectors directly shift to the Jointing carousel for Jointing the Socket and spigot. The short length pipes taken from Cutting Section "P16" after some days or as required to joint.
7.1 Prodn. Data Recording and Data Type:
- @ P17: The Operators record the information of production performed on that line.
- Looking for Touch Screen Data Entry practice.
8. Packaging & Transfer (P18 – P19):
At this process, the pipes which ARE jointed from the previous process packed as per standards (for Product-A, 20 Pipes/Pallet) and shift to warehouse after Quality control check.
8.1 Prodn. Data Recording and Data Type:
- @ P17: The Packers record the information of production performed on that line.
- Looking for Touch Screen Data Entry practice.
8.2 Laboratory Tests & Quality Control.
- After P17/P18: The Lab. Technicians took daily random samples from Pipes and performs different test as per standards.
9. General Attributes in Data Recording.
Here I will mention some general attributes which are required on each process to be part of data recoding with special data requirements of that process.
- Date/Time
- Production Lines/Machines where data is record.
- Shifts (Day Shift & Night Shift)
- Operators (of that Process or Machine)
10. Main Possible Entities & Attributes.
- Workstation (can be called Work station, ID, Description)
- Process (Can be called Operations, ID, Description, belongs to Workstation)
- Employees (ID, Name, Designation, Nationality, Hiring Date, Process)
- Machines (ID, Description, Process, etc)
- Item (Can be Raw Materials, Finish Goods) – (ID, Description, Type, Green Weight, Fired Weight, Etc).
- Dryer Cars (ID, Description)
- Kiln Cars (Two Types TK/SK Cars - ID, Description)
- Shifts (ID, Description)
- UOM
- Lab. Test (ID, Description, Performed at Process, Ref. Standard No, etc).
- Test Parameters (ID, Description, belongs to test, UOM, Standard Limits “Min/Max”, etc)
*One Test can have more than 2 parameters to be performed
- Reject Reasons (ID, Description, belongs to process, etc)
- Dow Time Reasons (ID, Description, Belongs to Process)
*If there is any down time at any machine, so will be recorded with reason.
May still something is missing but I think it is enough to have the basic idea of the Process.
Thanks you in advance.
Regards
Zee
Reply With Quote
