This explanation does not provide a complete answer to the question, but it does address critical considerations in coming up with a good solution. So it is an answer, just not a complete one.
Since I was the one who discouraged "flags", I'll further qualify my previous answer. The bad flags I mentioned where used to keep track of related database rows that could otherwise be handled by proper, normalized table and relationship design. Such bad flags do not tell you anything about the state of the data objects that you want to store, rather where only for database organizational purposes. Boolean (yes/no) flags may be okay and even necessary for recording an existing, valid state of the object(s) you are storing. That's the first test for determining if the flags are okay.
The next consideration, for any type of flag, is whether or not the chosen database schema allows contradictory states to be stored. The answer to how you handle this situation can be complicated, but
- you might be able to change/augment/refactor the database schema to avoid or eliminate contradictory states, or
- be prepared to write all queries and updates to consider and handle such possible contradictions. Sometimes this is necessary if the real-world scenario itself allows such contradictions. For instance, perhaps you assign an employee to two positions, thinking that perhaps they can handle 3 positions, so you leave
is_indeterminate checked, but then after a week on the job they are overwhelmed and you realize that they could not handle more work, so their is_indeterminate value is unchecked. In that case, there was nothing of an automated fashion that could've fixed that scenario.
On the other hand, what if that employee were now given a 3rd position, but the is_indeterminate checkbox was not properly updated. Now the system has inaccurate data. Is that tolerable? Will the workplace have someone that runs reports to periodically audit and fix bad data? Will your update routines handle possible bad data, so that, for example, the employee isn't underpaid because it says they need more work? Etc. etc.
Per #1 suggestion above, a possible change would be to replace a basic Boolean flag column with a position counter column. A query could simply indicate if the position has the correct number of employees assigned and flag shortages or over-staffing... but this time the "flag" is dynamically determined in the report, not stored in the database where it can become outdated.
Regarding the pseudo-code... We're back to using a bad flag like staffing_stadus_id = 1 == complete. I'll admit that sometimes such a flag can be useful for efficiency or simplicity reasons... like a final stamp that means "it's all done and don't ever bother me with that position again". Maybe that's okay. BUT, on the other hand, simply by adding a record to the [Position_Employee] table should be sufficient to determine if that particular position is complete. In other words, a query looks for all related records and if there are sufficient employees assigned to the position, then it is dynamically determined to be complete... no need to update a separate column with specialized codes that will certainly become outdated either by bugs or people not updating the status appropriately. If the schema/app is designed properly, the only way to mark the position as complete is to actually assign employees properly to the correct table... No accidentally updating the status, but forget to actually add the employee that was put in the position.
But once again, it could be necessary to have a "status flag", for instance if you want to maintain a history of positions, but perhaps a position is not yet filled but also determined to be obsolete. In that case, you would indeed want a flag that you could use to mark it as obsolete.
This might all seem subtle and even contradictory, but go back to the original 2 points and consider why you are adding each flag and consider if it can be implemented in a more natural, relational-database-esque manner.
Also, why are there both employee_id and position_id FK columns in both the [Staffing] table AND the [Position_Employee] tables? Once again, this sets up possible contradictory columns that could be different/outdated between the two tables. Once an employee is chosen for the position, then a record is simply added to the [Position_Employee] table. Why add the employee_id to the [Staffing] table to then just copy it into the [Position_Employee]?
If there is another legitimate business reason for having the employee_id in the [Staffing] table, then schema needs to consider that requirement separately. For instance, perhaps you want to store a potential employee for manual approval, and they are not put into the position until final approval is obtained. Then a better relational-database-esque schema would be another table, perhaps a [Staffing_potential] table with a staffing_id column and employee_id column. Once they are approved, then move (atomic transaction of copy/insert then delete) from the [Staffing_potential] to the [Position_Employee] table. Once again, the staffing status is implied in what tables the rows exist rather than messing with flag columns.
Update
It's always difficult to judge from the questions and the context the reason for each particular detail, despite the attempt to describe those details. It is now apparent that your design and requirements are mature enough that not only are there probably many ways to accomplish what you need, but there is really no answer to "how I can do this best?" without a full iterative analysis. That can't be done on these forums. In fact, I started with Stack Exchange long ago enough that I previously would have rejected this question as "too broad". But the recent push for being "nice and open" has lead me to provide more wordy and specific answers. I won't take back the advise I posted, but I'll wrap up this answer with...
Especially for historical or archival reasons, it may indeed be necessary to store duplicate data or even have supplemental "flag" fields. As long as there is a documented purpose and reason for this, database normalization rules are not necessarily "broken". I ran into that often with financial data where it was critical to record unalterable total transaction amounts along with constituent rows, even though the total could have been obtained from the detail rows. BUT the key is that either documentation or column names or some other feature distinguish between the purposes of the fields, not to be mistaken for non-normalized data. It's up to the developer(s) to ensure that the purpose of such fields are not eventually used for reasons that break stable, normalized design. (FYI, it often good to have separate tables where such historical rows are kept, which are separate from the "working data".)
The fact that you are conscious and aware of the purpose of individual features of your schema is critical. I don't have any further advise about handling the indeterminate employment cases.
