Recently during development process I've faced file storage issue. I'm not sure which solution is the best one that's why I decided to write this question. I don't have a lot experience of database administration that's why any tip can be really useful for me.
In my application project I have several types of files that have relationship with specific tables, for example:
- Invoice has one scan
- User has one document
- Transaction has one receipt
- Building has many photos
All those files:
scan | document | receipt | photo have different extensions(pdf/jpg/png etc.).
I decided that files corresponding for each table will be stored as
BLOB datatype. Because of
BLOB usage there is need to store file extension somewhere in database.
I was considering few possible solutions: (For demonstration purposes I will attach simple tables visualization)
user | invoice | building_photo | transaction have require file stored as
MEDIUMBLOB, but extension of files is missing. What would be the best way of storing that? as
VARCHAR column in each table or as separate table?
Example of extension storage in each table:
This solution is the easiest one to implement, extension as additional
VARCHAR is handy when I'm inserting new data, but I'm repeating this information since I will have two files with the same extension.
Example of extension storage in separate table:
This solution seems to be more optimized but little bit harder to manage while inserting new data. I would be grateful for any suggestion about problem described above.
Another implementation that I've considered is to not store all files in separate tables but instead create one table called
file that will contains file
BLOB and then invoice | user | transaction | building etc.
will have relationship to that table. Visualization below:
In this case again I'm keeping extension in separate table, but file data as well. All files are in the same place but I'll end up with large table and a lot one to one relationships. If such solution makes sense also I would be grateful for option, or maybe there is another way of doing such things?