Hello all,

I have a database containing a couple tables: files and users. This relationship is many-to-many, so I also have a table called users_files_ref which holds foreign keys to both of the above tables.

Here's the schema of each table:

files -> file_id, file_name

users -> user_id, user_name

users_files_ref -> user_file_ref_id, user_id, file_id

I'm using Codeigniter to build a file host application, and I'm right in the middle of adding the functionality that enables users to upload files. This is where I'm running into my problem.

Once I add a file to the files table, I will need that new file's id to update the users_files_ref table. Right now I'm adding the record to the files table, and then I imagined I'd run a query to grab the last file added, so that I can get the ID, and then use that ID to insert the new users_files_ref record.

I know this will work on a small scale, but I imagine there is a better way of managing these records, especially in a heavy-traffic scenario.

I am new to relational database stuff but have been around PHP for a while, so please bear with me here :-)

I have primary and foreign keys set up correctly for the files, users, and users_files_ref tables, I'm just wondering how to manage the adding of file records for this scenario?

Thanks for any help provided, it's much appreciated.

-Wes