PostgreSQL Database Design – Should Images Be in Separate Tables?

database-designpostgresqlschema

I'm pretty new to web development and i'm creating a local guide app. Currently, I have the following table in the database:

Organization Table

Organization_Id(PK)
name
description
site
category_id

Description

  • organization: entity that describes a merchant
  • organization profile: page with organization's details (name, description, site, cover header_image, profile_image, detail_images)
    • header_image: organizations cover image
    • profile_image: organizations logo image
    • detail_images: images of the organization building

Relationships

An Organization has_one profile_image, has_one header_image, has_many details_images.

Question

What is the best aproach in terms of normalization to accomplish that?

  1. Add one column for the profile_image and another to header_image in Organization table.
  2. Create one table ProfileImage, other table HeaderImage with one_to_one relationship to Organization table and eventually another DetailImage table with one_to_many relationship.
  3. Create an Image table to store all the images paths table with a column image_type (profile, header, details) using a one_to_many relationship.
  4. Other option

Best Answer

I would go with the first option.

  • Create two columns, one for profile image, one for header image.
  • Store the path

You shouldn't be creating new tables for 1:1 relations. For both header and profile you want to store at most one image. That's what columns are for.

Even better. Normalize them all to the same format and appropriate sizes and save them in pkid/header.png, pkid/profile.png and then just make boolean flags has_image_header has_image_profile If you don't want to hardcode that path in the app, and you'd rather hardcode it in the database do it with a function,

CREATE FUNCTION get_path(pkid int, imgtype text)
RETURNS text
AS $$
  SELECT '/' || pkid::text || '/' || imgtype || '.png';
$$ LANGUAGE sql
IMMUTABLE;

SELECT get_path(432, 'header');
    get_path     
-----------------
 /432/header.png
(1 row)
Related Question