How to relate a table to other two?

Question

I'm trying to design a database that contains, among others, entities "Photo", "Venues" and "Products". Given both Venues and Products could contain a collection of photos... how could I tell whether the photo is from a Venue or a product?

Photos
    Id PK
    Venue_Id FK
    Product_Id FK

Venues
    ID PK
    Photos_Id FK

Products
    ID PK
    Photos_Id Fk

Answer 1

There is likely something wrong with your model. Mutual dependencies are very rare in relational database designs. This is what I assume you want to do:

create table photos
( photo_id ... not null primary key
, <additional attributes> );

create table venues
( venue_id ... not null primary key
, photo_id ... not null
      references photos (photo_id) 
, <additional attributes> );

create table products
( product_id ... not null primary key
, photo_id ... not null
      references photos (photo_id) 
, <additional attributes> );

To determine whether a photo belongs to a venue, product or both you could do something like:

select x.photo_id
     , case when y.photo_id is not null then 'Venue' end
     , case when z.photo_id is not null then 'Product' end
from photos x
left join venues y
    on x.photo_id = y.photo_id
left join products z
    on x.photo_id = z.photo_id