Need help linking userID fields

[RJ2001]

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 incharge of an entire building. Now I have a seperate Table named HD with 2 Fields ID and ACID (AC is for Area Coordinator someone in charge of a group of buidlings).

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 seperate 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.

[azimmer]

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 incharge of an entire building. Now I have a seperate Table named HD with 2 Fields ID and ACID (AC is for Area Coordinator someone in charge of a group of buidlings).

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 seperate 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.

I suggest you either add the USER_ID to the HD table or set up a new "connection table" with two columns: USER_ID and HD_ID