How should I manage my many-to-many relationships?

Use $this->db->insert_id() to get the id number of the row you just inserted. Further documentation here: http://codeigniter.com/user_guide/database/helpers.html

You're basically describing how it normally is done, with one important adjustment: how you retrieve the file_id of the file to be able to add it to users_files_ref.

Normally in a database environment you have many clients connecting at the same time, doing updates simultaneously. In such an environment you can't just get the file_id of the last file added - it might be someone elses file added in between your DB calls. You have to use functionality of the database to get the ID generated (e.g. SELECT @@IDENTITY on MSSQL) or generate the IDs in the application code somehow.

I think what you need is just this:

                   ----primary key-----users_files_ref -> | user_id, file_id |

How you get the the file_id is dependent on the code you're implementing. Your reasoning is correct. You already have the user_id and just need to get the file_id. With these values you can add a new row to user_files_ref.

When I need to do this I usually have a stored procedure with the help of a sequence that inserts the file and returns the sequence NEXTVAL as the output. This might be a way of implementing such cenario.

This is the code for an Oracle based stored procedure:

CREATE OR REPLACE PROCEDURE SP_IMPORT_FILE(FILE    IN  FILE.FILE%TYPE,                                           FILE_ID OUT NUMBER)ISBEGIN  SELECT SEQ_FILE.NEXTVAL INTO FILE_ID from DUAL;  INSERT INTO FILE (FILE_ID, FILE) VALUES (FILE_ID, FILE);END SP_IMPORT_FILE;