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 !
>