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 !