pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
To: pgsql-sql@postgresql.org
Subject: Re: 'image' table with relationships to different objects
Date: Tue, 9 Feb 2010 20:41:49 +0100
Message-ID: <20100209194149.GA22261@apartia.fr> (raw)
In-Reply-To: <4B714E12.1090207@archonet.com>
References: <20100209074909.GA11838@apartia.fr>
	<4B714E12.1090207@archonet.com>

On Tue, Feb 09, 2010 at 11:59:14AM +0000, 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)

Thank you Richard, this looks like the best solution. And the view is
handy.

-- 
http://www.critikart.net



view thread (8+ messages)  latest in thread

Message-ID: <20100209194149.GA22261@apartia.fr>
Permalink:  ../20100209194149.GA22261@apartia.fr/
Also on:    postgresql.org/message-id/20100209194149.GA22261@apartia.fr

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: vindex+lists-pgsql-sql@apartia.org
  Subject: Re: 'image' table with relationships to different objects
  In-Reply-To: <20100209194149.GA22261@apartia.fr>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox