I'm building a database for users in the office and someone asked if there was a way to have the badge photos stored in the database with their user info. I created the following table but I have no idea what I should set the data type as so I can store user.jpg files in PostgreSQL:
florida=# \d users
Column | Type | Modifiers
id | integer | not null
fname | character varying(20) | not null
lname | character varying(20) | not null
email | character varying(50) | not null
office | integer | not null
position | character varying(40) | not null
dob | date |
"users_pkey" PRIMARY KEY, btree (id)
"users_email_key" UNIQUE, btree (email)
I would generally just add a new column but I have no idea what I should use as the data type. How can I do this or does SQL not support this?
Is it common in database world to store binary image files in the database itself or do I store them on a file server and link the path to the file in SQL? If the latter, then would I still use 'bytea' data type or was that only if I store the binary files into the database itself?
Is it common in database world to store binary image files in the database itself or do I store them on a file server and link the path to the file in SQL?
There are several reasons for both approaches. Personally I like to keep them in the database.
Arguments for keeping binary data in the database
everything in one place
storing them is part of the transaction
no need to do file system houskeeping
no need to worry about distributing files across directories because filesystems tend to have problems if you store millions of files in a single directory
It does have some disadvantages though:
database backup gets harder because the size quickly grows
no direct access from e.g. a web server. So if the images are used in a web application the application server would need to server them. That can be a performance problem (though there are ways to still make that fast)
you can't easily access them from other applications. e.g. a word processor
The main reason why most peoply strongly believe images should not be stored in the database is mainly the size of the database (backups) and speed of access.
If you do choose to store them in the filesystem, simply create a varchar (text) column to hold the path. bytea should only be used for "real" binary data.