one table is related to two other tables is that a good idea?



  • Usually in a 1 to 1 releationship two tables are related. But today I saw a database in which one table is related to two other tables, something like this:

    enter image description here

    Example data:

    media: 
    

    id | item_id | type | filename
    100 | 200 | video | video200.mp4
    101 | 201 | video | video201.mp4
    201 | 501 | image | awesome_photograph.png
    202 | 502 | image | awesome_photoshoot.png
    203 | 503 | image | awesome_icon.gif

    video:

    id | duration
    200 | 10.026666
    201 | 32.00

    image:

    id | bitdepth
    500 | 24
    501 | 24
    502 | 24
    503 | 8

    Personally, I think this is a very strange database design because of this:

    • the column type is holding 'duplicate' data for example 'image' is stored 3 times. Maybe it would be better to introduce a mediaType table, but that might have been an overkill.

    However, I can see one advantage:

    • the table media acts like a parent to both video and image

    Questions:

    Is it ok to design it like this or is this completely wrong?



  • It is ok. Doctrine calls this approach Inheritance Mapping.

    https://www.doctrine-project.org/projects/doctrine-orm/en/2.11/reference/inheritance-mapping.html


Log in to reply
 

Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2