Storing images in database or in file system

Images in database:

Advantages:

1. Secured
2. Easy backup and Recovery
3. Simple queries

Disadvantages:

1. Large size of database
2. Performance degradation of database with heavy images
3. Slower access speed

Images in file system:

Advantages:

1. Easier access
2. Increased performance (database’s performance will not be affected)
3. Faster access (File system access is faster than database access)

Disadvantages:

1. Less secure
2. Complex process to backup and restore
3. Added complexity while storage

(Suggested way: Create an ID, copy the ID, change the filename of the image and store the image to the database)

Suggestion:

Store Images in file system if:

1. Number of images are huge
2. Database space is limited
3. Available database is costlier
4. No problem in securing the images (Try setting permission over the images)

Store Images in database if:

1. Number of images are considerably less (Try storing images in separate table)
2. Not worried about the size of the database space (If you have enough space)
3. Database cost can be met
4. Scared about the security over the images in filesystem

Tips:

1. While the image is uploaded, check for the format of the image (Not all formats are viewable in the webpage)
2. Use compression algorithms to compress the size of the image
3. Check if the image uploaded is really an image (There are chances of uploading any PHP scripts to the image location)
4. Create a thumbnail image for every image uploaded.
5. While storing the image in the file system, create a table in the database with Auto-incrementing attribute that creates an ID for every image uploaded. Rename the image of any name to imageID.ext and imageID.thumb.ext (Naming conventions can be in any form that helps you to identify the image). In this way the images can be fetched from the file system instantly

Keys in SQL

1. Primary Key
2. Candidate Key
3. Foreign Key
4. Alternate Key
5. Composite or Compound Key
6. Unique Key

Candidate Key :

A Column with

• Unique values
• No null values

[A Table can have more than one column with above attributes. All these columns are candidate keys]

Primary Key:

A Column with

• Unique values
• No null values

[A Table can have more than one column with above attributes. But one of these column can be a primary key]

(Info: A table can have only one primary key but any number of candidate keys)

Alternate Key

When a table contains more than one candidate key (say 3 candidate keys), after selecting a column as a Primary key, other 2 columns are called as an Alternate Key

Unique Key:

A Column

• with Unique values
• can have null values

(Info: A table may not necessarily contain all the keys, but at least a primary key)

Composite Key:

Two or more columns collectively used to identify a row

Example: 

1. Identifying a student in a student table using his name and his father name

2. Identifying a student in a student table using his name, father name & Birth-date

Foreign Key:

• Presence of a column in a table which is a primary key for another table.
• This column is responsible for establishing relationship between two tables.

Multi-tenant and Single-tenant architecture

Multi-tenant architecture website:

1. Single instance
2. Data from multiple companies/users stored in a same database server
3. Data are partitioned to prevent mismatching
4. Standard and common configuration, templates, styles for all companies/users
5. No individual considerations
6. Any modifications are common to all
7. Customization for single company/user is difficult
8. Advantages:
1. Safe - Any changes / modifications are common to all
2. Meets the needs of most of the companies/users

Single-tenant architecture website:

1. Separate instance and infrastructure for every company/user
2. Used by companies/user when they need personal customization
3. Every company/user has a distinct database server
4. Advantages:
1. More Secure - No data migration/ mismatching
2. Preferred if the company needs a legal requirement

Tags: Multi-tenant and Single-tenant architecture, Multi-tenant, Single-tenant