Creating new workforce database (many workers, about 10 companies). Boss wants to use SSN as PK, but I think that may be a bad idea. They want to allow for multiple, duplicate SSNs (in case of same worker being hired by different companies), but only one of the duplicate entries will be allowed to have an "active" checkbox set to true at any given time (the workers current company affiliation).
Anyway so my question is this. What would be the easiest way to prevent more than one of these check boxes from being checked? Say there is one guy SSN 555-55-5555 who has worked for 3 companies. So that SSN is in the DB three times and one 'active' box is checked. When someone tries to check a different one, i want it to stop and ask the user "are you really, really sure?". I was thinking in the before update event of the input form I would have access loop through and look for any of the same ssn having a 'true' checkbox but that wouldn't stop someone that is actually going through the tables themselves...
any ideas welcome thks