Thank you Alexandre for clarifying this for me.
--- In firebird-support@yahoogroups.com, Alexandre Benson Smith <iblist@...>
wrote:
>
> Em 7/6/2013 01:37, jwcane2003 escreveu:
> > I am building an image database and want to know locations of all copies of
> > images. An image can be displayed in one or more locations: a web page, a
> > show, a book, a commercial gallery, someone's residence, etc. Any of these
> > locations can have multiple images. How is this best tracked, recorded and
> > returned as a query result?
> >
> > One solution is to create a table called ImageLocs, having foreign key
> > references to each image and to each image's location.
> >
> > I cannot get past the idea of a separate table for each location type:
> > gallery, show, book, magazine, web page, residence (the latter with a FK
> > link to the contacts table), etc. Each location table contains a location
> > name (show or book title, etc) and foreign keys to the image table and to
> > the location table(s).
> >
> > But then, ImageLocs will have lots of blank, foreign key cells, because
> > only one or very few locations will contain any given image.
> >
> > For example, if I publish a book with 100 images, ImageLocs will have
> > records containing that book's primary key (PK) 100 times, alongside the
> > PKs of each of its images, etc. Seems redundant, or is this the standard
> > practice?
> >
> > Or is there a better way?
> >
>
> Hi !
>
> Perhaps I misunderstand your message, but I will answer as I understood it.
>
> I think you could have something like:
>
> Images (Table)
> ImageID integer (PK)
> Image BLOB
> Name varchar
>
> Locations (Table)
> LocationID integer (PK)
> Description varchar
>
> ImageLocation (Table)
> ImageLocationID integer (PK)
> ImageID integer (FK to Images)
> LocationID integer (FK to Locations)
> AdditionalInfo varchar
>
> If you want to show all the locations you have an image you will need a
> query like this:
> select
> I.ImageID, I.Name, L.Description, IL.AdditionalInfo
> from
> Images I join
> ImageLocations IL on (IL.ImageID = I.ImageID) join
> Locations L on (L.LocationID = IL.LocationID)
> where
> I.ImageID = ?
>
> If you want to show all the images for a desired location you will need
> a query like this:
> select
> I.ImageID, I.Name, L.Description, IL.AdditionalInfo
> from
> Images I join
> ImageLocations IL on (IL.ImageID = I.ImageID) join
> Locations L on (L.LocationID = IL.LocationID)
> where
> L.LocationID = ?
>
> It's a classic many to many relationship. Did I missed something ?
>
> Of course you could have additional colluns like "Type" to indicate if
> it is a Book, a WebSite and so on. It could be a typed column (Integer)
> or you could have another table. I would go with another table...
>
> see you !
>
