I am capturing work orders. Each work order repair will involve different parts. For instance, one work order repair to a specific room might require a new door handle and a new window. Another might require 2 new windows but no door handle. Each part (sku), individually, must have an approval code for why that part was chosen. Sometimes, the approval code is pretty straightforward. For instance, the approval code might state that it was the least cost part that met the requirements and the approval code for that is “least cost”. But sometimes the approval code is “sole source” meaning that there was no discussion or research around the least cost part but it was automatically ordered from a single supplier. When this occurs, I need one more field: “Sole Source Explanation”. It is essentially a text field where more explanation is needed to justify why there was no research and this was a sole source part. It usually is because there is only one supplier who makes the part but there can be other reasons as well. How should I design the relationships between tables to capture this Sole Source Explanation field? Again, it will only be used when the approval code selection “Sole Source” is chosen but not when another value is chosen like “least cost”.
I have the following design so far below. Trying to figure out where to put the Sole Source Explanation table and how it should relate to the other tables. Thank you for any help.