What is the best way to store the address of files / pictures in the database

Question

MySQL

The question has always been asked who stores links to files / pictures in the database.

I see several options here:

1. store only the image name file.doc
2. store the image name and path relative to the download folder certificate/file.doc
3. store full path to file /srv/web/.../upload/certificate/file.doc
4. store all information about the file in a separate table, and store the ID of this record in the table we need
5. change the name of the image to the record ID in the database

I use option 1 for its simplicity and ease of use, which option do you use and why?

Answer 1

I use the second option, in the first case, if the number of files is large, then the names may match.
in option 3, there will be problems when moving to another server. I didn’t think about the fourth option, it seems to me that in the second everything is simple and clear.

Answer 2

4th option.
1. In a separate table, you can store the original file name, while keeping it on a server with a clever file organization, like: /ab/bc/de/abbcde.doc
2. The original file name is stored, which means that if user 1 uploaded file. doc, and the second user uploaded file.doc, then downloading both files will result in file.doc, not file(1)
.doc pictures/videos) duplicates.
Well, in general, this layer of abstraction does not bother anyone.

Answer 3

It should be borne in mind that the first option will not work if you need to store a lot of pictures. Searching in a folder will take an impressive amount of time, so it's better to split it into subfolders and store the relative path in the database

Answer 4

Store the offset in the database (can be divided by the sector size or more than 4096 ...) and the file size, with due red-eye, you can wrap both numbers in 64bit long and use it as a file identifier, store the files themselves in one large container (you don’t have to bother too much with file systems and put directly into / dev / sda), as a result - the fastest access to files (faster - only if you organize caching for the task yourself) and the most inconvenient maintenance with frequent deletions / changes to files (write only to the end of the container, according to the end of the place - a complete reorganization of the storage, with the issuance of new id ... but this may be an acceptable fee and in some tasks you don't even have to pay it).
p.s. did you think this was a joke? It just all depends on the task and how the data is used.
The above method was used for a long time for a non-web project, it provided the 'maximum possible' performance when backing up, reading and adding new files, it allowed organizing versioning 'out of the box', ...

Answer 5

The first option is more flexible, since the path to the image can be specified by a separate directive in the application's config file.
For example, in jang the second option is used - also very good, for each image field, in the model, you can set your own path to the image (in fact, specify the directory where the file will be uploaded, relative to the general upload directory).
As for the full path, it’s not very convenient, since the binding to the file location is hard.
On the 4th option, this is somehow strange, why create another table in which to store the record id? Now I don't see the point.
I would rather use the first and second options, and the choice between them depended on the application that I am writing. The fourth - provided that I have a lot of data in the table, which are needed with varying degrees of relevance. Those that were more in demand were in table A, and those that were less in demand were in B, and were contacted by id.

Answer 6

I have a 2 + 4 mix
, there is a separate photos table, where image urls for different sizes are stored (generated in the background)
in objects (users or something else) IDs for photos.
the name of the folder in which the picture will be stored and the file name are generated. folder names look like u_{user_id}, pictures are truncated md5 from the content. Thus, I hardly care about checking the uniqueness of names.

Answer 7

Well, it's probably worth clarifying what exactly is stored in the database. If this is a user profile and the path to a single avatar, then you can use 1 option. But difficulties may arise if you want to separate everything, for example, by users or by types of pictures (avatars separately in the avatars folder, etc.). Of course, you can write all this in logic, but there will almost certainly be difficulties with the number of edits if such an alteration occurs too late. Therefore, IMHO, option 2 is preferable.
And if these are user albums with an arbitrary number of pictures (and you need a link table), then I like option 4 more.

Answer 8

In my opinion, the first option is the best. This gives flexibility in organizing file storage. That is, today your folder with files is called “files”, and tomorrow you decide to call it “storage”. If you also store part of the path along with the file name, you will have to edit a bunch of entries. It is much easier, in my opinion, to specify the path to the directory with files in some kind of config, and store only names in the database.
Indeed, the organization of files will be greatly simplified: for example, you can sort them into separate directories at any time ('storage/music', 'storage/images', etc.).
> 5. change the name of the image to the record ID in the database
This option does not conflict with the first one. However, the appropriateness of such a decision depends on the specific situation.

Answer 9

Watching what files and for what.
If this is a user avatar, then you don’t need to store it at all - rename it to [ID].png and put it in the /avatars/ folder. This can be done with all files tied to the Primary Key.
If at the same time you need to save the file name, then again you can rename it to ID, and store the name in the database (not suitable if you have caching).
You can put it in the /files/[ID]/file.png folder. In general,
the solution depends on the specific task.
The full path is, of course, redundant and inflexible.

Answer 10

Gee, and even files can be stored directly in the database, in BLOB.

Answer 11

I do this - in the table field I enter the file names through the md5 separator (in general, they come out unique), and the pictures themselves are in the folder, for example, images/2014/08/17/ (year/month/day).
Thus, I can pull any image from the database, based on the date field of my entry in the table, parse it for the year, month and day - add any file name (based on the separator (maybe "comma")), and voila)
Ahem. And if, for example, you need to take reduced copies, then simply enter the line " _s/ " after our images/2014/08/17/ (from the example). The output is images/2014/08/17/_s/{ file name }. I think this is a fairly convenient way, albeit for my tasks