1

I had a Movie entity and an Image entity where a Movie should have an Image and I put them in a one-to-one relationship -

Movie
-----
id (PK)
title

Image
-----
id (PK)
base64
movie_id (FK to Movie, Unique)

I made Movie the parent entity based on the logic that "a Movie can exist without an Image, but an Image cannot exist without a Movie" - which did make sense. But then came Director, Actor, User etc, each of which too, requires to have an Image.

I want an Image 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 Image as child of other entities. But adding new foreign-keys to Image seemed a bad approach -

  • this will require more schema changes in future
  • only one of the foreign-key fields is going to have a non-Null value

Also, the initial logic doesn't hold anymore - an Image now can exist without a Movie because it might be an Image of something else.

To solve the issue -

  1. I considered to make Image the parent entity -
Image
-----
id (PK)
base64

Movie
-----
id (PK)
title
image_id (FK to Image, Unique)

Entities are required to keep track of their own Image and no schema change in existing Image entity, but -

  • the idea of Image being parent in a one-to-one relationship with Movie (or other entities) somehow felt wrong
  • two different types of entities can now reference the same Image
  • now I'm loosing the cascade delete of Image entity
  1. I considered having lookup table like MovieImage -
Movie
-----
id (PK)
title

Image
-----
id (PK)
base64

MovieImage
----------
movie_id (PK, FK to Movie)
image_id (FK to Image, Unique)

This would do, but -

  • an Image can sill be referenced by two different types of entities
  • it complicates a bit the models and data operations at application level
  • still loosing the cascading

So, what would be the best approach to go for and what are the general design practices in such scenario?

atiyar
  • 7,762
  • 6
  • 34
  • 75
  • https://stackoverflow.com/questions/4050784/defining-multiple-foreign-keys-in-one-table-to-many-tables/4051523#4051523 – Damir Sudarevic Jul 25 '20 at 14:57
  • "I'm losing the cascade delete of Image when a Movie is deleted." Image isn't the child of Movie anymore, so this isn't really a loss. Your option 1 is the better option. – Gilbert Le Blanc Jul 27 '20 at 13:40

2 Answers2

1

In such scenarios, you can use morph relations.

Image schema

id | filename | imageable_type | imageable_id

Now your imageable_type will store to what entity this image belongs to, and imageable_id will store the id for that entity.

https://devdojo.com/tnylea/understanding-polymorphic-relationships

OR you could do something that is explained in detail here.

MySQL - Conditional Foreign Key Constraints

Aashish gaba
  • 1,726
  • 1
  • 5
  • 14
  • Thanks for your answer. I'm expecting one-to-one relationship. That will mark any foreign-key in `Image` table as Unique. But with your suggestion `imageable_id` must be a non-Unique field. – atiyar Jul 25 '20 at 22:10
0
  1. You create a table for each entity <Entity>Image that is one-to-zero-to-one with the entity and one-to-one with image. This is the optional column approach, works well enough, but if that isn't clicking with you there are other options.
  2. Since there is really no good way to determine a "duplicate" image in a database (unless this is all a wild metaphor), instead create a one-to-many relationship between Image and the necessary entities that require/will display images. If there is no image, you can just choose one image as a placeholder.
  3. Corollary to item 2 - if you have an entity that may or may not have an image associated with it, or mutliple images associated with it, then you would want to model those as their own tables.
  • If I make the relationship one-to-many between and Image (where is the parent), then I'll end up having multiple foreign-key columns in the Image table and for any Image entry only one of those columns will contain a non-NULL value. Isn't so? – atiyar Jul 24 '20 at 20:45
  • No, `Image` is independent of its related entities. If you do a one-to-many relationship between an entity and `Image` the physical representation will be a junction table `Image` with the primary key `(,)`. There would be no need to create a relation from the entity to `Image`. –  Jul 24 '20 at 20:49