I want to add multiple job roles for different durations in designation history table . how can i get it in ABAP?

Question

I have 2 tables i.e employee details and employee designation history.

Employee details table - EMPID , Name , Age ,Gender. (EmpID - Primary Key)

Designation history table - EMPID , Startdate , Enddate , JobRole.

I know there needs to be one more key in designation history table to add multiple records for given employee ID ,which one should i use ?

So that i can get output like this - Sumedh was Associated SAP developer from 2021 - 2022 then from 2022 - 2024 he was sr. Developer then again got demoted to Associate sap developer in 2024-25.

Answer 1

I assume you just omitted the MANDT field for brevity. It needs to be the first of every tables primary key if you want your table to be client-specified. What follows next? Well, there are several options:

Option 1: use EMPID and Startdate as primary key. You see that a lot being used in older SAP database schemas, like table ADRP, for example. This of course assumes that nobody will be reassigned more than once per day.

Option 2: Add another column which counts the assignments for that employee. Then the primary key would be EMPID and ASSIGNMENT_NUM.

Option 3: Do it like BOPF does. Instead of wondering what the primary key should be, give every table a single primary key field DB_KEY which is a UUID, generated the moment the database entry is generated (You can use the class cl_system_uuid for generating them). Then never worry about primary keys again.

Which one is "the best" option? Sorry, but that's more of a religious question.