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


  • 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. – Greg Burghardt Apr 17 '18 at 17:38
  • now alignment is correct – wti Apr 17 '18 at 21:12
  • Not sure what this has to do with auto-increment. Date design problem exists however yo allocate the primary key. – James Anderson May 26 '18 at 10:38

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.

| improve this answer | |

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.