It might be tempting to store binary data in one of your database's table. Just don't do it. Even if a lot of ORMs do support uploading binary data into a database, it does not mean you should do it.
There are a lot of reasons against storing binary data (or BLOBs) in your database
This is obvious: Having more (binary) data in your database leads to much longer backup runs of your pg_dump
or mysqldump
commands. Backing up files is much faster than doing a database with the same binary data.
Having your binary data inside a filesystem allows you to do incremental backups, lowering the overall size. This is not possible if your database contains the binary data. A database dump can not be used for incremental backups.
There might be the time that one of your customers deletes an important file which has been stored in your database. It's definitely possible to restore this file from of one your database backup dumps but it takes time. Restoring a file from a filesystem backup or dedicated backup application is much faster.
Having binary data stored in your database means that each requested file must pulled from the database. Depending upon the number of requests, this is probate way to paralyze your database server.
Table customer {
id integer [not null, increment]
}
// This can be used as a template for local files or object storages
Table file {
id integer [not null, increment]
customer_id integer [not null, ref: > customer.id]
// after uploading the file, it is renamed to this UUID and moved to {local_file.customer_id}/{local_file.uuid} relative to a defined directory by your application; don't store absolute paths!
uuid uuid [not null]
// original filename
filename char(255) [not null]
// the uploaded file's MIME type; this is stored so the application can send the correct HTTP Content-Type headers
mime_type char(255) [null]
// size in bytes, also used for sending the correct Content-Length HTTP header
size integer [not null]
// maybe a hashsum to prevent duplication
// hashsum char(64) [not null]
}