2

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)
0

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.

  • Fantastic, makes total sense. I think i'll incorporate your'e idea. – js02sixty Aug 19 '16 at 19:07
3

Yes, your idea can work. Add an is_primary boolean column to images:

ALTER TABLE images
ADD is_primary boolean NOT NULL;

and enforce the "No more than one primary picture per product" rule with a partial index:

CREATE UNIQUE INDEX images_primary_image_constraint
ON images (product_id)
WHERE is_primary;

The above index will ensure uniqueness of product_id among rows where is_primary is set. If you try to specify a second primary image for some product, the uniqueness will immediately be violated and the operation will fail.

  • That's great. Thank You. I'm glad I wasnt too far off. – js02sixty Aug 19 '16 at 19:05
0

Business Rule:

  1. Each product can have more than one Product Images.
  2. Each product images must contain atleast one Product.
  3. Each Image belongs to one or more Product Images.
  4. Each Product Image must contain atleast one Images.

Data Model:

The logical data model for the above business requirements may looks like as follows:

enter image description here

In real world, I have seen more than one primary images for a product, also, a image is used as for multiple products, so it is better to consider the future requirements as well. Also, instead of directly doing to physical design, I request you to go through the conceptual data model to physical design, so that it is easier for you to get the clear picture, maintain and accomadate the future requirements in easiest way.

As per your exact requirements, the physical design may change. I hope this may help you, Thanks!

  • That's a great resource you shared, and thank you for the visual. Ill definitely use an association table and use Neil's display_order. – js02sixty Aug 19 '16 at 19:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.