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?
