How do I manage many-to-many relationships?

Question

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

Here is a diagram 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 use Codeigniter to create a file host application, and I'm right in the middle of adding features that allow users to upload files. Here I ran into a problem.

As soon as I add the file to the file table, I will need this new file identifier to update the users_files_ref table. Right now I am adding an entry to the file table and then imagine that I ran a query to capture the last added file so that I can get the identifier and then use this identifier to insert a new users_files_ref entry.

I know this will work on a small scale, but I think there is a better way to manage these records, especially in a heavy traffic scenario.

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

, , users_files_ref, , ?

, .

-Wes

Answer 1

$this->db->insert_id(), , . : http://codeigniter.com/user_guide/database/helpers.html

Answer 2

, :

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

file_id , . . user_id, _. user_files_ref.

, , NEXTVAL . .

Oracle:

CREATE OR REPLACE PROCEDURE SP_IMPORT_FILE(FILE    IN  FILE.FILE%TYPE,
                                           FILE_ID OUT NUMBER)

IS

BEGIN

  SELECT SEQ_FILE.NEXTVAL INTO FILE_ID from DUAL;

  INSERT INTO FILE (FILE_ID, FILE) VALUES (FILE_ID, FILE);

END SP_IMPORT_FILE;

Answer 3

, , : _ , user_files_ref.

Typically, in a database environment, multiple clients connect at the same time, performing updates simultaneously. In such an environment, you cannot just get file_id from the last file added - it can be someone added to the elses file between your database calls. You must use the database functionality to obtain the generated identifier (for example, SELECT @@IDENTITYin MSSQL) or somehow generate identifiers in the application code.