I'm trying to design database tables to track files uploaded by end users.
Files can be uploaded under different contexts.
Each context is a different table.
As an example:
- An employee can upload multiple "receipt" files against a "weekly expense" entry.
- An employee can upload multiple "photo" files against a "pets" entry.
The entities / tables in this (contrived) scenario are:
Employees (Id, Name)
Expenses (Id, Date, RequestedRefund, RequestedByEmployeeId)
Pets (Id, Name, Type, BelongsToEmployeeId)
I already have a table called
Files which tracks information about the file itself:
Files (Id, Name, Size, Extension, Folder)
My question is around how to map a expense receipt upload and pet photo upload against the file record. I know I can do this in 2 ways:
Have one generic mapping table:
GenericFileMap (FileId, ContextId, Type)
FileIdis the id of the file record
ContextIdis the id of the context record that i'm trying to retrieve
Typeis the field to describe the context itself
In this scenario, I'd fetch all expense receipts for an employee like so:
SELECT * FROM Employee e INNER JOIN Expenses ex ON e.Id = ex.RequestedByEmployeeId INNER JOIN GenericFileMap g ON g.ContextId = ex.Id AND g.Type = 'expense' INNER JOIN Files f ON g.FileId = f.Id
Have separate mapping table for each entity / context table:
ExpenseFiles (ExpenseId, FileId)
PetFiles (PetId, FileId)
The query for receipts would be:
SELECT * FROM Employee e INNER JOIN Expenses ex ON e.Id = ex.RequestedByEmployeeId INNER JOIN ExpenseFiles ef ON ef.ExpenseId = ex.Id INNER JOIN Files f ON ef.FileId = f.Id
I believe the 1st option is not the "normalized" way to do things in a relational database. But the only reason i'm considering this is, in my particular situation, there are at least 15 different contexts from which a user can upload / attach files.
The only benefit i see from the 1st option is the time I'd save creating and duplicating tables and stored procedures (one each for CRUD) for each and every context.
However, it does seem that the 2nd option is the "right" way to do things.
- Which of the two options is the smarter approach?
- Which of the two is better for performance in the long run (I'll definitely put in the extra effort needed if the second option will be more performant) ?