0

I am designing a simple image gallery that the user can set for album primary picture lets say similar to Facebook that user will be able to select the cover of the album from the pictures.

I believe that to achieve this I will need to add another bool column to Images table to call it IsCoverPicture. Then I will set this value for specific image to true. But later if they would like to change the cover image?

The way I do it is to set all value first to false and then set the new selected value to true.

Here I just wonder whether this is the correct way of doing this or is there any specific store procedure or anything by using Entity Framework that would handle this for me automatically when I set a new true value for the new cover picture it would take care of the rest for me and go and set the rest false?

akd
  • 6,538
  • 16
  • 70
  • 112
  • The quick and dirty way to do it without changing your schema is to simply add a trigger on the table that applies the constraint you wish. – Jane S Aug 21 '14 at 03:02

2 Answers2

1

First solution: Change your album table to contain pictures and the cover. This way only one picture can be set to be the cover of the album.

Another idea is to add a unique constraint on album column and the new boolean column. Your image table will contains at least these two columns: AlbumId and IsCover. Simply add a unique constraint on these two columns: Unique constraint on multiple columns

Community
  • 1
  • 1
meziantou
  • 20,589
  • 7
  • 64
  • 83
  • Could you be more specific about your second solution pls? Why am I adding 2 columns for? jsut adding one column wouldn't be enough? – akd Mar 21 '14 at 10:13
0

I suggest you normalise your schema. You need a table Cover with columns PictureId and AlbumId something like this.

CREATE TABLE Cover (
  AlbumId INT,
  PictureId INT, 
  PRIMARY KEY (AlbumId,PictureId)
)

With your current schema, to change the cover image you need to update two rows, and a single delete can leave you with no cover image. With a normalised schema update anomalies cannot occur.

If you declare the foreign key constraints then you will also be protected from the delete problem.

Peter Wone
  • 17,965
  • 12
  • 82
  • 134