Using Autoincrement Primary keys as Foreign Keys vs. unique codes

Question

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???

wti

• Greg Burghardt: I'm sorry, but I'm not sure what you are asking. Is the alignment of the columns incorrect in the data base example in your question? That might help.
• wti: now alignment is correct
• James Anderson: Not sure what this has to do with auto-increment. Date design problem exists however yo allocate the primary key.

Answer 1

If you need to keep the foreign key the same it seems to me that you need an additional table one for the advisor role and then another for the person filling the role. The person table would have a foreign key pointing back to the role table. That's where you would keep dates etc.

I think it's much better to use reference primary keys in foreign key relationships. Referencing business keys from a foreign key often leads to problems, in my experience.

Answer 2

It seems like your Business Adviser is a position (Could be a territory.) that has one person assigned to it at a time. There would be another table that manages those assignments, so different people can be assigned to that position (Code 2 in this case) at different Start Dates. The assignments table is basically a many-to-many relationship between the people and the position (Adviser).

This structure also allows one person to be assigned to two Business Adviser positions simultaneously. The business may not do this regularly, but it could in a case where someone has to cover until a replacement is found.

Otherwise, just change the Business adviser ID on the client, but now you have no history of assignments.