Switching from One-to-Many to Many-to-Many structure
I think I know what to do here, but just wanted to run it by to make sure I would be doing it correctly and not missing any tricks.
At the moment I have a One-to-Many structure:
tbl_Users
UserID (PK)
User
etc
tbl_Lodges
LodgeID (PK)
Lodge
UserID
etc
So any given Lodge has a single User associated, and that User can log in to an admin section and update the Lodge details.
We have increasingly found a need to allow multiple users to be able to log in and update these details, hence the switch to a many-to-many structure.
Am I right in thinking the way to do this is to create an interlinking table with the fields:
LodgeID
UserID
And the quickest way to create this table would just be to make a copy of the tbl_Lodges, delete all the fields other than LodgeID and UserID, and remove the PK from the UserID field?
One other question there - does the linking table need a third field to act as a Primary Key?
And lastly, in order to attach users to lodges would it just be a case of updating a two field form with the fields UserID and LodgeID? That's what I assume, although it involves looking up the lodge and user, noting the IDs and then adding a new record to the linking table. But wondered if I'm missing a trick there and there might be a more intuitive way of associating users with lodges?
Hope that makes sense.
Thank you!