title: Storing File Paths in a Database
body: <h2>Background Information</h2>
  
  <p>I am working on a project that has file path fields that are either a "one to one" relationship (Invoice object has only a single file is allowed) or "one to many" relationship (a Claim object has many files that can be added to it). </p>
  
  <h2>What I Have So Far</h2>
  
  <p>I currently have two ideas. </p>
  
  <p><strong>1) One Database for All Files</strong></p>
  
  <p>I have a [Claim] Database</p>
  
  <pre><code>Claim (PK) --- Name --- Address
  1              Joe      Some Place
  </code></pre>
  
  <p>I have an [Invoice] Database</p>
  
  <pre><code>Invoice (PK) --- Vendor --- FilePathID
  100              12         1
  </code></pre>
  
  <p>And an [Attachments] Database</p>
  
  <pre><code>UID (PK) --- Claim --- Description --- FilePath
  1            NULL      Invoice         path\to\invoice
  2            1         Receipt         path\to\receipt
  3            1         Image           path\to\image
  </code></pre>
  
  <p>This would work in the following way:</p>
  
  <p>The [Invoice] file would be explicitly defined ([Attachment] ID 1 in this case) in the [Invoice] object. Then files would be attached to the [Claim] object by a linking number (Claim 1 in the case above) in the "Attachments" database. This way we can do a query to list all attachments for [Claim] one, but at the same time keep the [Invoice] file in the same [Attachments] database linked by the UID number for the file. This allows all uploads to be mapped in a single database.</p>
  
  <p>My only concern with this method is that on the file that is uploaded for an [Invoice] the Claim field is left to NULL.</p>
  
  <p><strong>2) File Paths Are Stored In Multiple Databases</strong></p>
  
  <p>This is almost identical to the above example but instead of linking the FilePath in the [Attachments] database it is defined directly in the [Invoices] database. Like this:</p>
  
  <p>I have a [Claim] Database</p>
  
  <pre><code>Claim (PK) --- Name --- Address
  1              Joe      Some Place
  </code></pre>
  
  <p>I have an [Invoice] Database</p>
  
  <pre><code>Invoice (PK) --- Vendor --- FilePath
  100              12         path\to\invoice
  </code></pre>
  
  <p>And an [Attachments] Database</p>
  
  <pre><code>UID (PK) --- Claim --- Description --- FilePath
  1            1         Receipt         path\to\receipt
  2            1         Image           path\to\image
  </code></pre>
  
  <p>This method allows for the Claim field in the [Attachments] database to never be NULL, but it separates the area where the [Attachments] FilePath is found.</p>
  
  <p>I am very torn between the two methods at this point. Is there a preferred method or best practice to do this or is it really just what one feels comfortable doing?</p>
  
  <h3>Update</h3>
  
  <p>We are having to do this for at least 4 objects. Not just two.</p>
  
  <p><br></p>
  
  <p>Thanks very much in advance and any help is very much appreciated,</p>
  
  <p>Brandon</p>