Hi,
I’m looking for best practices or practical recommendations on what to do here. Many thanks in advance.
I have to manage PERSON records in a database table that keeps only one record per individual. Each of these has or may have a value in some fields that uniquely identify them, like for example a club membership number that may or may not be assigned at the time the record is entered in the database. My application does not control the numbers that are assigned to each individual, just keeps to use them later.
I would like to ensure that when the application user edits a PERSON’s record, that the number they put in PERSON.MembershipNumber has not been already assigned to someone else.
I tried using the Validator but did not work and upon further reading I realized that the documentation says to use it when you want to insert a new record, not when you want to update an existing one.
From a performance perspective, perhaps the best is to mark the field as unique in the database table and let the RDBMS engine handle the check. This way, if the User input say Alice’s MembershipNumber in Robert’s record and then tries to save Robert’s record, the RDBMS would not be allow the operation. This is good, but I don’t know how to convey to the user the fact that the operation failed because the MembershipNumber was already in use by Alice.
I was thinking that another way to accomplish this would be to fire a search from the page form when the user leaves the Membership Number field (so the check happens before they hit the submit button) but I have no idea on how to do that. I don’t like the idea of having a “Check if this number is not already in use” button associated to the field because if the user did not click it, then the record could be updated with a duplicate Membership Number.
Any guidance on the above will be greatly appreciated!
Many thanks,
Alex
Last updated: