StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POStoring File Paths in a Database
text
Body
copied!<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>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload