Обсуждение: 'image' table with relationships to different objects
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,
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 >
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
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
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. >
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
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 ourproprietary 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 informationproprietary 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 thatany unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have receivedthis e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mailimmediately. Thank you.
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.