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 | Other
SQL Server | Other

Additional SQL Server features and topics not covered by specific categories

0 comments

Answer accepted by question author

  1. Tom Phillips 17,786 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.

    Was this answer helpful?

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 134.1K Reputation points MVP Volunteer Moderator
    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.

    Was this answer helpful?

    1 person found this answer helpful.

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.