I am creating a massive website for my Residence Life Department, using MS SQL as its backbone and PHP as the interface.

The part I need help with-

I have a user table with a lot of user info id, name, password, etc. It also contains a jobTitle field. For instance someone can log in who is a HD (Hall Director) who is in charge of an entire building. Now I have a separate Table named HD with 2 Fields ID and ACID (AC is for Area Coordinator someone in charge of a group of buildings).

I want to be able to assign an auto generated ID to the HD table that will link to the specified user from the USER table, without putting a separate field in the USER table. Why I want to do this is because I will have forms that are linked through many foreign keys and joins that refer back to these two ID's, but the person can switch buildings from year to year which then I would generate a new ID for them all without having to create a new USER table entry for them.