title: Multiple tables referencing one table using multiple junction tables
body: <p>I have the following table that will store a file (image, PDF, etc.)</p>
  
  <pre><code>CREATE TABLE `tbl_file` (
      `id` INT(10),
      `size` FLOAT,
      `name` VARCHAR(45),
      `type` VARCHAR(16),
      `content` BLOB,
      `date_time` TIMESTAMP,
      PRIMARY KEY (`id`) 
  )
  </code></pre>
  
  <p>There are many tables that will have files (tbl_order &amp; tbl_payment) and I want to use only one table to store files (tbl_file). However, some tables may have multiple files for each row. </p>
  
  <p>For example, there are multiple files for one order (invoice, PO, BL, contract) and there may be multiple files for each payment, etc. </p>
  
  <p>So I made the following junction tables for each table that may have multiple files ( I didn't include the foreign key code). </p>
  
  <pre><code>CREATE TABLE `tbl_order_file` (
      `order_id` INT(10),
      `file_id` INT(10),
      PRIMARY KEY (`order_id`, `file_id`),
  )
  </code></pre>
  
  <p>and </p>
  
  <pre><code>CREATE TABLE `tbl_payment_file` (
      `payment_id` INT(10),
      `file_id` INT(10),
      PRIMARY KEY (`payment_id`, `file_id`),
  )
  </code></pre>
  
  <p>the problem here, is that one file can be related to both tables. Maybe it's an advantage rather than a problem but I would like to know if there is a better way to do this or to find a way to restrict the file so it's only referenced in one table.</p>
  
  <p>I am using MySQL with innodb engine.</p>