I have an Access database that I am redesigning in SQL Server. It is for a package carrier, and tracks the performance of the teams that load and unload aircraft.

Originally, my Team table included the following fields:

TeamNumber (pk)
FacilityID (pk)
ShiftID (pk)

The table had a composite key of the first 3 fields.

In the new version, I'm adding a TeamID surrogate key, and not using the composite key anymore (though I keep the fields of course). And I'm moving ManagerID to a separate table where I can more easily track manager changes (having a start and end date for each manager associated with the TeamID).

Here's my question: is this design a good one for handling team changes over time? I don't mean just a StartTime change...I mean when (as has happened twice before) the team's NUMBER changes but the manager and other details remain the same. Or, the facility discontinues using a particular team number for a while, and perhaps years later they start using it again.

When looking at historical performance, I want to make sure that, for example, if what is now team 5 was known as team 10 last year, the trend of that team's performance (from related tables) doesn't break on the date they changed team numbers. And that if, say, team 40 hasn't been used in years, and the facility starts using that number again, the new team 40's performance won't be trended along with the old team 40's performance.

Does that make any sense?