pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed'image' table with relationships to different objects
8+ messages / 6 participants
[nested] [flat]
* 'image' table with relationships to different objects
@ 2010-02-09 07:49 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-02-09 11:34 ` Re: 'image' table with relationships to different objects Michael Lourant <lourant@gmail.com>
2010-02-09 11:59 ` Re: 'image' table with relationships to different objects Richard Huxton <dev@archonet.com>
2010-02-11 11:05 ` Re: 'image' table with relationships to different objects Jasen Betts <jasen@xnet.co.nz>
0 siblings, 3 replies; 8+ messages in thread
From: Louis-David Mitterrand @ 2010-02-09 07:49 UTC (permalink / raw)
To: pgsql-sql
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,
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: 'image' table with relationships to different objects
2010-02-09 07:49 'image' table with relationships to different objects Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
@ 2010-02-09 11:34 ` Michael Lourant <lourant@gmail.com>
2 siblings, 0 replies; 8+ messages in thread
From: Michael Lourant @ 2010-02-09 11:34 UTC (permalink / raw)
To: 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@apartia.org>:
> 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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: 'image' table with relationships to different objects
2010-02-09 07:49 'image' table with relationships to different objects Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
@ 2010-02-09 11:59 ` Richard Huxton <dev@archonet.com>
2010-02-09 15:01 ` Re: 'image' table with relationships to different objects Rob Sargent <robjsargent@gmail.com>
2010-02-09 19:41 ` Re: 'image' table with relationships to different objects Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-02-10 19:07 ` Re: 'image' table with relationships to different objects Justin Graf <justin@magwerks.com>
2 siblings, 3 replies; 8+ messages in thread
From: Richard Huxton @ 2010-02-09 11:59 UTC (permalink / raw)
To: pgsql-sql
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)
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: 'image' table with relationships to different objects
2010-02-09 07:49 'image' table with relationships to different objects Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-02-09 11:59 ` Re: 'image' table with relationships to different objects Richard Huxton <dev@archonet.com>
@ 2010-02-09 15:01 ` Rob Sargent <robjsargent@gmail.com>
2010-02-10 17:29 ` Re: 'image' table with relationships to different objects Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2 siblings, 1 reply; 8+ messages in thread
From: Rob Sargent @ 2010-02-09 15:01 UTC (permalink / raw)
To: 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:
> 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)
>
> 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.
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: 'image' table with relationships to different objects
2010-02-09 07:49 'image' table with relationships to different objects Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-02-09 11:59 ` Re: 'image' table with relationships to different objects Richard Huxton <dev@archonet.com>
2010-02-09 15:01 ` Re: 'image' table with relationships to different objects Rob Sargent <robjsargent@gmail.com>
@ 2010-02-10 17:29 ` Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
0 siblings, 0 replies; 8+ messages in thread
From: Louis-David Mitterrand @ 2010-02-10 17:29 UTC (permalink / raw)
To: pgsql-sql
On Tue, Feb 09, 2010 at 08:01:35AM -0700, Rob Sargent wrote:
> 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
This is intriguing. How do I manage the auto-incrementing 'id' serial on
children tables 'person', 'location' and 'event'?
Thanks,
--
http://www.critikart.net
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: 'image' table with relationships to different objects
2010-02-09 07:49 'image' table with relationships to different objects Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-02-09 11:59 ` Re: 'image' table with relationships to different objects Richard Huxton <dev@archonet.com>
@ 2010-02-09 19:41 ` Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2 siblings, 0 replies; 8+ messages in thread
From: Louis-David Mitterrand @ 2010-02-09 19:41 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: 'image' table with relationships to different objects
2010-02-09 07:49 'image' table with relationships to different objects Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-02-09 11:59 ` Re: 'image' table with relationships to different objects Richard Huxton <dev@archonet.com>
@ 2010-02-10 19:07 ` Justin Graf <justin@magwerks.com>
2 siblings, 0 replies; 8+ messages in thread
From: Justin Graf @ 2010-02-10 19:07 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; pgsql-sql; vindex+lists-pgsql-sql@apartia.org
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: 'image' table with relationships to different objects
2010-02-09 07:49 'image' table with relationships to different objects Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
@ 2010-02-11 11:05 ` Jasen Betts <jasen@xnet.co.nz>
2 siblings, 0 replies; 8+ messages in thread
From: Jasen Betts @ 2010-02-11 11:05 UTC (permalink / raw)
To: pgsql-sql
On 2010-02-09, Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org> wrote:
> Hello,
>
> In my database I have different object types (person, location, event,
> etc.) all of which can have several images attached.
can one image be several people?
can one image be both event and location?
> 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)?
probably several join tables
image_location image_person image_event
with uniques and cascades where needed.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2010-02-11 11:05 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-02-09 07:49 'image' table with relationships to different objects Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-02-09 11:34 ` Michael Lourant <lourant@gmail.com>
2010-02-09 11:59 ` Richard Huxton <dev@archonet.com>
2010-02-09 15:01 ` Rob Sargent <robjsargent@gmail.com>
2010-02-10 17:29 ` Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-02-09 19:41 ` Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-02-10 19:07 ` Justin Graf <justin@magwerks.com>
2010-02-11 11:05 ` Jasen Betts <jasen@xnet.co.nz>
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