Sorry it took so long to get back to you, thank you for all your suggestions. I am sure it seemed like a fortnight, haha.
My first instinct would be to change the names of all the "ID" columns to follow the standard of tablenameID
I agree, I have changed the ID columns to reflect the tablename
ID. This will hopefully make things easier when making the FK relationships.
Also, if the ScrapCode table is only a reference table, then I wouldn't use an ID on that table. I'd limit it to just the code and use the code in the Primary table. Humans will like you better for it when they have to read the Primary table.
The scrap code will be referenced by more than one table, one of the tables I didn't get a chance to list. So there are scrapped jobs from the primary table but in the jobs there are solutions which can also be scrapped. The scrap code will be a drop down field that will say for example: ScrapCodeID | ScrapReason
1 | Caught Fire
2 | Defects in raw materials
Also, will each BatchNumber in the Primary table only be for 1 ItemID? If not (and they probably shouldn't be), then consider having a separate Batch table and Item table.
The batch ID follows the product through the whole process, now it starts off with a few letters / numbers and then expands as it goes through the process. The batch ID will be referenced when we need to trace the processes all the way back to the beginning.
I think you also need a RunType table to ensure the quality of the data entered into that column in the Primary table.
Append "ID" to the StartOperator and EndOperator columns.
Consider NOT abbreviating the word "Description" in the Recipe table.
I have added a run type table and changed the RunType to RunTypeID. I have also updated the Start and End operator IDs and the word description has been fully spelt out now.
"TimeInSolution" is fairly non-descript. How will you identify such a duration in the table. Decimal hours, minutes, seconds, hhhhhhhh:mi:ss, fortnights, or what??? The same applies for TotalRunTime. WHAT will the unit of measure be for these two columns? That will allow us to define more descript column names.
The TimeInSolution will be a drop down where an operator can select the time based on a set incremental between 5 and 30 minutes. (5min, 10min, 15min, 20min, 25min, 30min) I tried to use fortnights but they shot that idea down. Now the TotalRunTime, will be a calculated time based on the StartDT and EndDT. (It will be used for analytics later on.)
Consider NOT using the Nbr abbreviation in WorkOrderNbr in the Primary table. Either replace it with the '#" sign or spell it out so people don't have to guess which abbreviation you may have used.
I have changed the Nbr to Number.
Consider NOT using columns called "Active". If they are guaranteed to only contain the two obvious states, consider calling them "IsActive". But, to bullet-proof your code for the future, consider using "StatusCode" instead and add a StatusCode table for DRI.Just so I know I understand this correctly the StatusCode would be a table with 2 rows correct? Then the ID of either Active/Inactive would be an ID field in each of the tables?
Sorry if I misunderstood!
On the start and end operator thing. What if you have 3 operators? Consider logging each operator in a separate table along with their start and end DTs.
There will only be 2 operators on every project, since the process takes up to 14 hours to run there are only 2 shifts for the operators. So one starts it at 6am and another operator ends it before the end of their shift.
Consider adding a CreatedOn and CreatedBy column to every table. Same goes for a ModifiedOn and ModifiedBy column. It seems like a grand waste of time and space but unless you have a really good audit system running, it'll save your sanity in the future. Depending on the attention to detail that your Developers put into their code, consider adding a trigger to the tables to ensure that ModifiedOn and Modified by will be filled in if by nothing else other than GETDATE() and ORIGINAL_LOGIN(), respectively.
Thank you so much for this suggestion! It makes perfect sense even if it is a little more work up front. Now in the primary table has multiple integer fields based on the ID fields when selected in from the drop down menus. Is this still an okay way to do things?