title: Uniquely identify images of multiple entity types from a single table - general design practices
body: <p>I had a <code>Movie</code> entity and an <code>Image</code> entity where a <code>Movie</code> should have an <code>Image</code> and I put them in a one-to-one relationship -</p>
  <pre><code>Movie
  -----
  id (PK)
  title
  
  Image
  -----
  id (PK)
  base64
  movie_id (FK to Movie, Unique)
  </code></pre>
  <p>I made <code>Movie</code> the parent entity based on the logic that &quot;a <code>Movie</code> can exist without an <code>Image</code>, but an <code>Image</code> cannot exist without a <code>Movie</code>&quot; - which did make sense. But then came Director, Actor, User etc, each of which too, requires to have an <code>Image</code>.</p>
  <p>I want an <code>Image</code> to be uniquely identifiable through any other entities, i.e. same Image should not belong to any two different entities (of similar or different types). That is very much possible if I keep <code>Image</code> as child of other entities. But adding new foreign-keys to <code>Image</code> seemed a bad approach -</p>
  <ul>
  <li>this will require more schema changes in future</li>
  <li>only one of the foreign-key fields is going to have a non-Null value</li>
  </ul>
  <p>Also, the initial logic doesn't hold anymore - an <code>Image</code> now can exist without a <code>Movie</code> because it might be an <code>Image</code> of something else.</p>
  <p>To solve the issue -</p>
  <ol>
  <li>I considered to make <code>Image</code> the parent entity -</li>
  </ol>
  <pre><code>Image
  -----
  id (PK)
  base64
  
  Movie
  -----
  id (PK)
  title
  image_id (FK to Image, Unique)
  </code></pre>
  <p>Entities are required to keep track of their own <code>Image</code> and no schema change in existing <code>Image</code> entity, but -</p>
  <ul>
  <li>the idea of <code>Image</code> being parent in a one-to-one relationship with <code>Movie</code> (or other entities) somehow felt wrong</li>
  <li>two different types of entities can now reference the same <code>Image</code></li>
  <li>now I'm loosing the cascade delete of <code>Image</code> entity</li>
  </ul>
  <ol start="2">
  <li>I considered having lookup table like <code>MovieImage</code> -</li>
  </ol>
  <pre><code>Movie
  -----
  id (PK)
  title
  
  Image
  -----
  id (PK)
  base64
  
  MovieImage
  ----------
  movie_id (PK, FK to Movie)
  image_id (FK to Image, Unique)
  </code></pre>
  <p>This would do, but -</p>
  <ul>
  <li>an <code>Image</code> can sill be referenced by two different types of entities</li>
  <li>it complicates a bit the models and data operations at application level</li>
  <li>still loosing the cascading</li>
  </ul>
  <p>So, what would be the best approach to go for and what are the general design practices in such scenario?</p>