Re: Clues on modeling a really simple concept

From: Rob <rmpsfdbs_at_gmail.com>
Date: Sun, 12 Apr 2009 13:56:13 -0700 (PDT)
Message-ID: <90779a64-191a-43e2-aa5c-324d5f7f67fa_at_v1g2000prd.googlegroups.com>

On Apr 6, 2:01 pm, Spike <fau..._at_gmail.com> wrote:
> Hi everybody!
>
> I'm trying to model a photo gallery, and i have a doubt on how to
> implement it.
>
> I have a table for users. I have another table for pictures. And
> finally, i have a table for galleries (which are sets of pictures).
>
> Both galleries and pictures belongs to a single user, but a single
> picture can be used on many galleries.
>
> So, i have something like this:
>
> users --> pictures --> pictures_to_galleries --> galleries
>
> My question is: Should i add a relationship between galleries and
> users?
>
> I would like to query which galleries a user has, and i don't really
> know if it is worth adding a foreign key rather than doing a three-
> level join query each time i want to obtain this data.
>
> Thank you very much!

In the interest of "fun", and because you asked "what I would do", I present a entirely different approach.

OP Spike describes a universe consisting of Persons who upload Pictures that get organized into sets called Galleries. In different ways, posters jefftyzzer and Walter Mitty describe similar database designs involving 3 entity types and 3 relationships, roughly (using jefftyzzer naming):

Entity Types(3): person, picture, gallery.

Relationships)(3): person owns picture, person owns gallery, gallery contains picture

Rules:      Each person exclusively owns zero or more pictures.
               Each person exclusively owns zero or more galleries.
               Each gallery contains a non-unique set of pictures.

I propose a solution with only two entity types and two relationships. I'm not claiming this solution is better, only that it provides a single represention mechanism for both relationships and a different way of representing aggregation.

My two entity types are person and picture. My two relationships are:

   person owns picture(s).
   person aggregates picture sets.

For brevity, I have minimized non-key attributes in the two entity relation schemas. My interest here is in the relationships. Primary keys in all relations are specified as system-generated. (Some people call these "surrogate keys". This is my design choice -- it is not required.) Syntax is MS SQL Server.

CREATE TABLE person(

   person_pk int identity(1,1) primary key,    person_name varchar(250) not null
);
CREATE TABLE picture(

   picture_pk int identity(1,1) primary key,    picture_name varchar(250) not null
);

Notice that neither entity relation has a foreign key.

I implement the relationships independently using the "Aggregate-Link" database data structure. (Described at www.sfdbs.com under "FastTrack".) This data structure realizes one relationship with two relations generically referred to as the relationship's aggregate- and link relations.

/*
 * CREATE RELATIONSHIP person owns picture(s): "PersonPicture".
 */
CREATE TABLE PersonPictureAgg(
   PersonPictureAgg_pk                int identity(1,1)  primary key,
   PersonPictureAgg_person_fk      int not null references person

(person_pk)
);
CREATE TABLE PersonPictureLnk(

   PersonPictureLnk_pk                            int identity(1,1)

primary key,

   PersonPictureLnk_PersonPictureAgg_fk int not null references PersonPictureAgg(PersonPictureAgg_pk),

   PersonPictureLnk_picture_fk int unique not null references picture(picture_pk)
);
/*

• CREATE RELATIONSHIP person aggregates picture sets: "PersonGallery". */ CREATE TABLE PersonGalleryAgg( PersonGalleryAgg_pk int identity(1,1) primary key, PersonGalleryAgg_person_fk int not null references person (person_pk) ); CREATE TABLE PersonGallerylnk( PersonGalleryLnk_pk int identity(1,1) primary key, PersonGalleryLnk_PersonGalleryAgg_fk int not null references PersonGalleryAgg(PersonGalleryAgg_pk), PersonGalleryLnk_picture_fk int not null references picture (picture_pk), unique (PersonGalleryLnk_PersonGalleryAgg_fk,PersonGallerylnk_picture_fk) );

This database schema would not automatically delete photos for deleted persons. That behavior could be achieved with a stored procedure triggered by a person delete. Note that a Junction Table would not work for the 2nd relationship because multiple galleries would be indistinguishable.

Have at it!!

Rob Received on Sun Apr 12 2009 - 22:56:13 CEST