I am currently building a database for an employment agency. As part of the database there will be table for candidates and vacancies. Each of these tables will have a field that will be publicly exposed in order to uniquely identify the record. Examples of this public exposure would be: -
For the candidate table - a number or string to uniquely identify the candidate. This would be "publicly" exposed by the fact that it would be given to the candidate upon registration.
For the vacancy table - a number or string uniquely identifying that vacancy. This would be publicly exposed, for example, through the URL when a vacancy is displayed... http://ift.tt/1QlaDcZ.
Obviously the PK of the table is unique to each record and, technically speaking, can be implemented here. However, I wish to create a unique non-PK field to use in it's place. The reason for this is not because of security/SQL injection but has more to do with business practice - I wish to not publicly expose the number of records the database has for vacancies or candidates.
I am asking this on here as it doesn't seem to be a question with an obvious or easily-found answer.
I am thinking about using a 10-digit randomly generated integer for this purpose, and using EXISTS to verify the uniqueness of the sequence prior to creating the record.
I would appreciate any input on if a 10 digit random integer would be appropriate/overkill etc and I would also like advice on whether it would be best to use this in place of the primary key or whether I should just keep the PK as the standard INT AUTO_NUMBER? As you would expect, the primary key of these records will be used in many joins etc and I do not want to unduly degrade the database performance.
Any insight would be greatly appreciated as this is not a scenario I have encountered before.
FYI I am building the application in Yii2.
Edit: -
I have done some more research into this scenario and I have concluded that it is probably best to have a standard autonumber as the PK and to keep the reference number as a separate field in the record.
However, I am still less than sure about the format of this number. From what I can gather, the number should be just that - a number, not a string. And while there is some debate on whether or not the number should be entirely random or not, I think it would be best that it is. A 12 digit number would seem to be as good a solution as any, and the first number in the sequence should be >= 1, to avoid trailing zero issues. This gives me up to 899,999,999,999 variations, which should be plenty. For readability, when displaying this number, displaying in blocks of 4 numbers in a format of XXXX-XXXX-XXXX.
The generation of the number would be automated and quite straight-forward by way of a BEFORE SAVE event in the Yii ActiveRecord. I can use $intTheNumber = rand(100000000000, 999999999999) and check to see if this number is unique in the DB prior to saving in order to saving the record, regenerating the random number if required.
In summary: -
1) Create a bigint UNIQUE NOT NULL field.
2) Random number must never start with 0 so that trailing zero issues are avoided.
3) Have a number range of 100,000,000,000 to 999,999,999,999 allowing 899,999,999,999 variations
4) During the BEFORE SAVE event, assign the random number after using EXISTS to confirm the number will be unique
5) Any time the random number is displayed, display in format of XXXX-XXXX-XXXX for the sake of readibility
Any thoughts?
Aucun commentaire:
Enregistrer un commentaire