I'm setting up a table to keep track of employee information - name, phone, email, etc. However, there's always the possiblity that we could hire two John Smiths, and not everyone necesarily has an email address. What should I use for a unique identifier? Should I create an "employee number" column?
SQL guys - what to use for a unique key?
- Thread starter notfred
- Start date
a unique key is nice so there is a way to search down to one entry and no exact duplicates records can e made.
Internally databases use keys to find and correlate data when do quieres.
Most modern DBs have an option to create a autonumber so everytime a new entry is made it will put another number into thta column, many people like have that as their primary key.
Internally databases use keys to find and correlate data when do quieres.
Most modern DBs have an option to create a autonumber so everytime a new entry is made it will put another number into thta column, many people like have that as their primary key.
I would second using the SSN if it is available. If not then you will have to use some sort of employee id, which may already exist. At our office we use initials as a key and modify the middle initial when a duplicate occurs (for instance, my initials are JBM, if someone else joined the company with those same initials they would get JXM, the JYM). This only really works for a small company like ours.
I dn't think it's necessary to have SSN in this table (it's jsut for contact information) so I think I'll just use an arbitrary value that's incremented everytime a new employee is added.
SSN may not be a good choice. How many people have access and how secure is the data? If its being stored with personal info and it gets into the wrong hands...well you can figure it out.
Perhaps a phone number may work. How many John Smiths's live togehter?
-PAB
The company I work for makes DB software that is used to track employees (among many other things). Or software has the ability to use any field as a unique ID, but by far the most common for any company we install at is using an autonumber.
Phone is rarely an option because their can be employees who don't have a phone, but work in a department with a common phone number (you know, call up, "Hey is John Smith there?"). I also rarely see SSN.
Phone is rarely an option because their can be employees who don't have a phone, but work in a department with a common phone number (you know, call up, "Hey is John Smith there?"). I also rarely see SSN.
SSN is known as a natural primary key, but as you said, it's not appropriate for all data models. In fact, I think some states have legislation these days specifically covering acceptable handling of SSN because there's so much identity fraud out there.
Like Ameesh said, most databases will auto-generate an integer primary key value for you.
SSN is not a good idea for a database unless it is secured. I recently finished working at a large company that still used SSN for tracking their employees and they are moving towards an employee id since many employees have expressed concern about their SSN being available on reports and in the many applications that deal with employees.
TRENDING THREADS
-
Discussion Zen 5 Speculation (EPYC Turin and Strix Point/Granite Ridge - Ryzen 9000)- Started by DisEnchantment
- Replies: 25K
-
Discussion Intel Meteor, Arrow, Lunar & Panther Lakes + WCL Discussion Threads
- Started by Tigerick
- Replies: 24K
-
Discussion Intel current and future Lakes & Rapids thread- Started by TheF34RChannel
- Replies: 23K
-
-
Facebook
Twitter