Message-ID: <4B7303FB.5090501@magwerks.com>
Date: Wed, 10 Feb 2010 14:07:39 -0500
From: Justin Graf <justin@magwerks.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
	rv:1.9.1.7) Gecko/20100111 Lightning/1.0b1 Thunderbird/3.0.1
MIME-Version: 1.0
To: Richard Huxton <dev@archonet.com>, pgsql-sql@postgresql.org,
	vindex+lists-pgsql-sql@apartia.org
Subject: Re: 'image' table with relationships to different objects
References: <20100209074909.GA11838@apartia.fr>
 <4B714E12.1090207@archonet.com>
In-Reply-To: <4B714E12.1090207@archonet.com>
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: 7bit

On 2/9/2010 6:59 AM, Richard Huxton wrote:
> On 09/02/10 07:49, Louis-David Mitterrand wrote:
>> Hello,
>>
>> In my database I have different object types (person, location, event,
>> etc.) all of which can have several images attached.
>>
>> What is the best way to manage a single 'image' table with relationships
>> to (potentially) many different object types while keeping referrential
>> integrity (foreign keys)?
>
> The "clean" way to do this would be with a number of joining tables:
>
> images    (img_id, file_name, title ...)
> persons   (psn_id, first_name, last_name, ...)
> locations (loc_id, loc_name, lat, lon, ...)
> events    (evt_id, evt_name, starts_on, ends_on, ...)
>
> person_images   (psn_id, img_id)
> location_images (loc_id, img_id)
> event_images    (evt_id, img_id)


Another why that reduces the number of tables and simplifies the selects 
and allows linking to unlimited number of objects is something like this

is something like this
Create table images (img_id serial , img_data bytea );

Create table image_related( img_rel_id serial, img_rel_img_id int, 
img_link_key int, img_link_from char(10) );

Create table persons   (psn_id serial, first_name text, last_name text) ;
create table locations (loc_id serial, loc_name text) ;
create table events    (evt_id serial, evt_name text, starts_on 
timestamp, ends_on timestamp);

Insert into images values (default, null), (default, null), (default, null);

Insert into persons values ( default, 'me me', 'yes itsme');
Insert into locations values (default,  'I home');
Insert into events values (default, 'friends party', now(),  now() );
insert into image_related values (default, 1, 1, 'persons'), (default 
,2, 1, 'events'), (default ,3, 1, 'locations'), (default , 2, 1, 'persons');


Select img_data, first_name  from persons, images, image_related
     where img_id = img_rel_img_id
     and img_link_key = psn_id
     and img_link_from  = 'persons'


then create a rule on img_related before insert and update to make sure 
the parent records exist  for integrity checks.


All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored.

CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately.
Thank you.