So, you're writing a web application and you have several areas of the site where the user can upload files. My basic working method for this is to store the actual file on the server, and have a database table that connects the stored filename to the record it relates to.
My question is this: Should there be a different table for each "type" of file? Also, should the files be stored in context-related locations on the server, or all together?
Some examples: user profile photos, job application CVs, related documents on CMS pages, etc.
From your example, there is an argument for two tables, as you have files that can be associated with two different things.
- CVs, photos are associated with a user.
- attachments are associated with a CMS page.
If you put these in one table, (and you want to allow users to have more than one photo or cv) then you need two link-tables to associate files->users and files->cms_pages. Arguably this implies a HABTM relationship, which is not correct and allows for inconsistent data.
The two table approach is slightly cleaner and only allows files to be associated with the correct type of entity with a simple belongsTo relationship.
But I don't think there is any "right" answer to this question, unless you need to store different types of metadata for different filetypes.
Also be sure to store, or be able to calculate, the mimetype for each file so it can be served correctly back to the browser, with the correct HTTP headers.
Answer author Cheekysoft
Tickanswer.com is providing the only single recommended solution of the question When is a file just a file? under the categories i.e database-design , . Our team of experts filter the best solution for you.