Just tell them no. As a developer, you know this is a very bad idea.
If a police badge number is used several times, how do you find out the name of the person using it?
Using a database select query.
Consider car registration numbers
A company car could be used by several people, at once, or over time.
The registration number is uniqueā¦ there can only be one such car, but āthe driverā could vary
Do not accept that EmployeeID has to be the database primary key.
If you try to do that, you will fail.
A hidden field PersonID should be the primary key, and restricted to be unique at the DB level
The EmployeeID / first name/surname / Title etc are all malleable attributes of the person.
2 Likes
That is not the case; I have a PK (ID I think).
How are you going to handle when Emily Schwarz Employee ID 109 now no longer works for the company and HR now says that John Smith is now Employee ID 109?
OK. New information. Now the rules have changed.
If PK is the primary key, and EmployeeID is an attribute of a person,
yes, you COULD set the field to be restricted as unique.
If you do that, then an attempt to save a new record with the same employeeID will generate an exception.
You trap that, and maybe then warn the user.
At the same time, you may need to issue a ROLLBACK statement, if any other things were saved at the same time which would depend upon the existence of that new record.
Iām not a fan of that exception approach (although there will be some here who areā¦)
In your shoes, I would first check to see if the EmployeeID existed using the select count(1) method, and prevent the user from Saving at all by showing something on the front end to show why they can not.
Leave the exception handling inā¦ it is always possible that in a multi-user scenario, someone ELSE manages to save such a record in the second between your test and your save attempt.
But if someone can re-use an employeeID
(which will happen if the client wants no more than a 4 digit reference, or they are like a police force, or ā¦)
then making that field unique will just make your future life a problem.
In my experience, if you ask a simple yes/no question of a client like ādo people have two jobsā, or ādo people re-use employee IDā
someone will say āNoā
ā¦ and a little voice from the back of the room may say āwell, except for Brianā¦ā
1 Like
Thank you for your inputs.
For testings, I added some Records with the same contents excepted the Employee_ID. As yoyu can see below, no problem to add them:
This is stupid and only done for the test (testing the Move thru the RowSet), but it works. (this screen shot comes from a different project).
Now, I will delete the file and add an āEmployee_Exit_Dateā Column (thanks Alberto), then continueā¦