Database Design – Modeling Product with Multiple Image URLs and Default Image

database-designpostgresql

Background: I'm using Postgres with Golang GORM library by jinzhu

Here is a basic representation of what I'm looking for, however I dont think it would be right to have a circular reference like this. Is there a better way to achive what I'm looking for? One idea of mine is to omit the primary_pictures_id and add a boolean field to the images table to flag a picture as the default (just not sure yet on how to enforce only one to be true)

products
    id
    name
    price
    primary_picture_id FK:images(id)

images
    id
    url
    products_id FK:products(id)

Best Answer

I would add a column for "display order" of the images, and have a policy of "use the first as the default"

You probably want your images showing up in a defined order (not random) anyways, so you will likely want such a column already. Your database might select them in a random order if you don't specify.

You might want to / have to re-use a picture for multiple products, so it's really a many-to-many structure

create table products (
  product_id int primary key, 
  ...
);

create table products_pictures (
  product_id int references products(product_id),
  picture_id int references pictures(picture_id),
  display_order smallint not null default 0,

  primary key (product_id, picture_id)
);

create table pictures (
  picture_id int primary key,
  ...
);

You'll want a trigger to re-order related pictures if you change the display order. There are a few postings on SO about that already.

Related Question