I have attached a screenshot of my table relationships. I am attempting to develop a database to 1) Manage Jobs 2) Monitor Production 3) Track Inventory.
I believe that the bulk of my design is correct, but I know that I have several problems....
1. tblJobSetup - Originally, the database was built to only monitor one Process. However, now that I am adding processes, the non FK fields (CoatingMils, TotalFT, etc) will vary depending on which process is selected. For example, the job terminology and specifications are dramatically different for Coating Jobs versus Welding Jobs. As a result, tblJobSetup needs to be altered (it was probably designed wrong in the first place). I have been advised to create an additional table to handle the details associated with each Process, but I am somewhat confused as to how I would go about that. I have also considered creating a separate Job Setup table for each Process, but that does not appear to be properly normalized.
2. Dealing with Materials, the prices are always subject to change. I would like to incorporate a FIFO approach, but I am not sure how to properly set up the tables or what code/queries are needed to make sure that the material usage table pulls the oldest shipped material first...(Hence the lack of relationships in the Material tables)
3. Monitoring Production requires that I calculate efficiencies that incorporate values associated with each facility...the values change over time like prices. Do I also need child tables to tblFacility that contain the values needed for calculations?
Thanks for any help, advice, or suggestions you may provide.
I believe that the bulk of my design is correct, but I know that I have several problems....
1. tblJobSetup - Originally, the database was built to only monitor one Process. However, now that I am adding processes, the non FK fields (CoatingMils, TotalFT, etc) will vary depending on which process is selected. For example, the job terminology and specifications are dramatically different for Coating Jobs versus Welding Jobs. As a result, tblJobSetup needs to be altered (it was probably designed wrong in the first place). I have been advised to create an additional table to handle the details associated with each Process, but I am somewhat confused as to how I would go about that. I have also considered creating a separate Job Setup table for each Process, but that does not appear to be properly normalized.
2. Dealing with Materials, the prices are always subject to change. I would like to incorporate a FIFO approach, but I am not sure how to properly set up the tables or what code/queries are needed to make sure that the material usage table pulls the oldest shipped material first...(Hence the lack of relationships in the Material tables)
3. Monitoring Production requires that I calculate efficiencies that incorporate values associated with each facility...the values change over time like prices. Do I also need child tables to tblFacility that contain the values needed for calculations?
Thanks for any help, advice, or suggestions you may provide.
