My current schema looks like the following:
ID | DisplayVal
-- ----------
1 1-H-3
2 2-H-3
3 3-J-4
In the above, the ID field is an IDENTITY INT field which is also used as and end user account Id. The DisplayVal is what they see on the screen.
A new client has provided their own Account Id values, but they are alpha-numeric, so they can't just go into the IDENTITY field. Here are my scenarios: I am looking for a scenario that would offer the best maintainability, end user experience, magnitude and impact of changes and testing/QA impact.
My first scenario was to add an Account Number column that would be a VARCHAR(x) and accommodate all types of Account Numbers. It would look like this:
ID | DisplayVal | AccountNumber
-- ---------- -------------
1 1-H-3 1
2 2-H-3 2
3 3-J-4 3
4 h389 h389
5 h-400-x h400
In the above, in the case of the first client, the seeded Identity which is the Account Id would be copied into the Account Number, but for the other client, there would still be a seeded Identity created, but their Account Number would be different and it may or may not match the Display Value.
My second scenario was to not add any columns and for clients that provide an Account Number, I would turn off IDENTITY INSERT and insert the new Id's and then turn identity insert back on. If a client did not provide an Account Number, I would auto-generate one, obviously trying to avoid collisions.
The third scenario was basically to leave the new Account Number as a legacy Account Number and create new identity values for all new records. This would require the end-user to become familiar with a new Account Number. It is probably the easiest, but not sure if there are any downsides.
If there is another scenario you know that would work well in this case, let me know.
