0

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.

sumedh patil
  • 59
  • 3
  • 8

1 Answers1

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.

Philipp
  • 67,764
  • 9
  • 118
  • 153
  • I thought of making Startdate as primary key as well but i think i read somewhere that its not a good idea to make dates as primary key? is that true? – sumedh patil Dec 01 '21 at 10:59
  • 1
    @sumedhpatil There are no absolute truths in software architecture. Every design has advantages or disadvantages which might or might not matter for your particular use-case. – Philipp Dec 01 '21 at 11:21
  • I will go with startdate as primary key then. Thank you so much. – sumedh patil Dec 01 '21 at 12:08