Re: 'image' table with relationships to different objects
От | Justin Graf |
---|---|
Тема | Re: 'image' table with relationships to different objects |
Дата | |
Msg-id | 4B7303FB.5090501@magwerks.com обсуждение исходный текст |
Ответ на | Re: 'image' table with relationships to different objects (Richard Huxton <dev@archonet.com>) |
Список | pgsql-sql |
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.
В списке pgsql-sql по дате отправления:
Предыдущее
От: Louis-David MitterrandДата:
Сообщение: Re: 'image' table with relationships to different objects