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

Thanks,

--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Hi There,

Maybe a table MEDIA_OBJECT with a ID column that will be exported as
FK to other tables and as many columns as media types you want to
store or a single column where you'll store the bytes of your media
file.

Hope it works!

__
Michael Lourant
"Let's warm them all..."


2010/2/9 Louis-David Mitterrand <vindex+lists-pgsql-sql (AT) apartia (DOT) org>:
--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

On 09/02/10 07:49, Louis-David Mitterrand wrote:
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)

You might then want a view over these joining tables to see what images
go where...

CREATE VIEW all_images AS
SELECT
i1.img_id,
i1.file_name,
'PERSON'::text AS link_type,
p.first_name || ' ' || p.last_name AS linked_name
FROM
images i1
JOIN person_images pi ON i1.img_id = pi.img_id
JOIN persons p ON pi.psn_id = p.psn_id
UNION ALL
SELECT
i2.img_id,
i2.file_name,
'LOCATION'::text AS link_type,
l.loc_name AS linked_name
FROM
images i2
JOIN location_images li ON i2.img_id = li.img_id
JOIN locations l ON li.loc_id = l.loc_id
....

You could do something clever with inheritance on the joining tables,
but it's better to keep things simple imho.

--
Richard Huxton
Archonet Ltd

--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

You can also invert this, making all the image owner share a common base
table and then images are dependent on that base

base (id, type) where type is an enumeration or some such
person (id, name, etc) where id is FK to base id
locations (id, address, etc) where id is FK to base.id
events(id, date, etc) where id is FK to base.id
images(id, baseid) where baseid is FK to base.id

views across base to the "data" tables for easier sql if desired
ORM: person location and event would inherit from base

On 02/09/2010 04:59 AM, Richard Huxton wrote:
--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

On Tue, Feb 09, 2010 at 11:59:14AM +0000, Richard Huxton wrote:
Thank you Richard, this looks like the best solution. And the view is
handy.

--
http://www.critikart.net

--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

On Tue, Feb 09, 2010 at 08:01:35AM -0700, Rob Sargent wrote:
This is intriguing. How do I manage the auto-incrementing 'id' serial on
children tables 'person', 'location' and 'event'?

Thanks,

--
http://www.critikart.net

--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

On 2/9/2010 6:59 AM, Richard Huxton wrote:

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.


--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

On 2010-02-09, Louis-David Mitterrand <vindex+lists-pgsql-sql (AT) apartia (DOT) org> wrote:
can one image be several people?

can one image be both event and location?

probably several join tables

image_location image_person image_event

with uniques and cascades where needed.

--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql