Storing File Paths in a Database

Question

Background Information

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).

What I Have So Far

I currently have two ideas.

1) One Database for All Files

I have a [Claim] Database

Claim (PK) --- Name --- Address
1              Joe      Some Place

I have an [Invoice] Database

Invoice (PK) --- Vendor --- FilePathID
100              12         1

And an [Attachments] Database

UID (PK) --- Claim --- Description --- FilePath
1            NULL      Invoice         path\to\invoice
2            1         Receipt         path\to\receipt
3            1         Image           path\to\image

This would work in the following way:

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.

My only concern with this method is that on the file that is uploaded for an [Invoice] the Claim field is left to NULL.

2) File Paths Are Stored In Multiple Databases

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:

I have a [Claim] Database

Claim (PK) --- Name --- Address
1              Joe      Some Place

I have an [Invoice] Database

Invoice (PK) --- Vendor --- FilePath
100              12         path\to\invoice

And an [Attachments] Database

UID (PK) --- Claim --- Description --- FilePath
1            1         Receipt         path\to\receipt
2            1         Image           path\to\image

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.

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?

Update

We are having to do this for at least 4 objects. Not just two.

Thanks very much in advance and any help is very much appreciated,

Brandon

Answer 1

If i where facing the problem, I would have a base table called Document for all document-like entities, having shared properties of all documents.
Having this base table, Attachment table will have a one-to-many relation to Document table.
Other document-like entities such as Invoice or Claim will have a one-to-one relation to the Document table.

Answer 2

Not sure this is "best practice", but here's an Option 3:

[Claim] table:

ClaimID (PK) --- Name --- Address
1                Joe      Some Place

[Invoice] table:

InvoiceID (PK) --- Vendor
100                12     

[Attachment] table:

AttachmentID (PK) --- Description --- FilePath
1                     Invoice         path\to\invoice
2                     Receipt         path\to\receipt
3                     Image           path\to\image

[ClaimAttachment] table:

ClaimID (FK) --- AttachmentID (FK)
1                2
1                3

[InvoiceAttachment] table:

InvoiceID (FK) --- AttachmentID (FK)
100                1

If you need the database to enforce that an invoice may only have one attachment, put a unique constraint on the Invoice column in the InvoiceAttachment table.

Alternatively if you want to only have one table to handle the attachment relationships, you could do something like this:

[AttachmentRlshp] table:

AttachmentID (FK) --- EntityID --- EntityType
1                     100          Invoice
2                     1            Claim
3                     1            Claim