How should I manage my many-to-many relationships?
- by wes
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
