Share via

Advice to select the best employee business key

Franck GUSTIN 21 Reputation points
2020-08-31T15:22:08.553+00:00

Hi, I'd like to get your opinion/advices to select the best employee business key in our system. I work on a multi-country, multi-company data warehouse project. You may find different HR systems all around the world, so I have to choose the appropriate fields to make sure that ID is unique for one employee and that it won't change. Based on your experience with Kimball projects, could you tell me which key you've used to identifiy employees. Company+HRID ? HRID+BirthDate+ThreeLettersCodeBasedOnFirstnameAndName ? Country+HRID ? Of course, using a different ID for each HR system would probably be the best, but because we gather data from lot of different systems, it will be difficult to maintain it over time. Could you tell me which key you would suggest on such project ? Thanks Franck

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,504 questions
0 comments
Accepted answer
  1. Tom Phillips 17,726 Reputation points
    2020-08-31T17:27:00.047+00:00

    From experience, I can tell you there likely is not a good business key for employees.

    It depends on your goal. You may be able to use Company + HRID in some cases. In others, the HRID changes ever time the employee changes department, or is rehired. This makes it hard to 100% of the time link them.

    You are better off using a surrogate key and mapping the business key to the surrogate key. This will allow you to manually link them in the future, if you find the HRID changed for rehires, etc.

    All other options you listed are not good. All those can and will change over time. If someone types the DOB wrong, and corrects it, it becomes a new employee.

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 108.8K Reputation points MVP
    2020-08-31T21:52:02.983+00:00

    I tend to agree with Tom. Have a table where you have all the key-like attributes, but use a surrogate key elsewhere. And if this is really complicated maybe some table in between so that two sets of keys can be the same surrogate key in other tables. This is for the situations where you find that two sets of keys are really the same person / employee. (Yeah, if the person has been employed in two companies in your system is that the same person or two?)

    I also agree with Tom that primary keys should generally be immutable.

    1 person found this answer helpful.