Re: [SQL] 'image' table with relationships to different objects
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 : > 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 > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] 'image' table with relationships to different objects
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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] 'image' table with relationships to different objects
On Tue, Feb 09, 2010 at 11:59:14AM +, 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] 'image' table with relationships to different objects
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. > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
