i’d like to push discussion on the same named thread again.
I also use auto increment primary keys as foreign keys - mostly for code tables. Every row also has a code for identifying entries for users (like ProductKey). Everything works fine till I need a validity period for entries in code tables.
e.g. every Client have a BusinessAdvisor:
Client BusinessAdvisor Name BusinessAdvisorID ID Code Name StartDate Company1 1 1 01 Schmidt 2017-04-01 Company2 4 2 02 Müller 2017-01-01 Company3 2 3 03 Schulze 2017-10-01 Company4 3 4 11 Meyer 2016-05-01 Company5 1 … Company6 2 48 02 Becker 2018-04-01 Company7 2
At 2018-04-01 Müller leave the company and Becker gets code '02' and therefore all clients of Müller.
For history of BusinessAdvisor it’s not allowed to change the entry with ID 2. This row is finished with setting an end date.
In this case it is possible to print Müller as BusinessAdvisor in historical reports (before 2018-04-01). Current reports/displays/processing should show/use Becker, but this entry of course use another ID.
My opinion is to solve this problem with a modification of database design…
Any other ideas???