Relationships mess

Question

Say I have a table ads, and each ad has several medias - held in a medias table. Each media can be one of several types. Types are in a media_types table.

So, for example, an ad has two medias: one of type desktop and one of type mobile.

I need the ads table to reference multiple rows in medias, but only 1 of any given type. I need also the number of types to be able to grow easily.

Edited schemas:

ads

CREATE TABLE `ads` (
  `id`               INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `media_desktop_id` INT(10) UNSIGNED NOT NULL,
  `media_mobile_id`  INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ads_media_desktop_id_foreign` (`media_desktop_id`),
  KEY `ads_media_mobile_id_foreign` (`media_mobile_id`),
  CONSTRAINT `ads_media_mobile_id_foreign` FOREIGN KEY (`media_mobile_id`) REFERENCES `medias` (`id`),
  CONSTRAINT `ads_media_desktop_id_foreign` FOREIGN KEY (`media_desktop_id`) REFERENCES `medias` (`id`)
)
  ENGINE = InnoDB
  AUTO_INCREMENT = 11
  DEFAULT CHARSET = utf8
  COLLATE = utf8_unicode_ci

medias

CREATE TABLE `medias` (
  `id`   INT(10) UNSIGNED             NOT NULL AUTO_INCREMENT,
  `type` INT(10) UNSIGNED             NOT NULL,
  `src`  TEXT COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `medias_type_foreign` (`type`),
  CONSTRAINT `medias_type_foreign` FOREIGN KEY (`type`) REFERENCES `media_types` (`id`)
)
  ENGINE = InnoDB
  AUTO_INCREMENT = 21
  DEFAULT CHARSET = utf8
  COLLATE = utf8_unicode_ci

media_types

CREATE TABLE `media_types` (
  `id`         INT(10) UNSIGNED        NOT NULL AUTO_INCREMENT,
  `created_at` TIMESTAMP               NULL     DEFAULT NULL,
  `updated_at` TIMESTAMP               NULL     DEFAULT NULL,
  `name`       VARCHAR(255)
               COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
)
  ENGINE = InnoDB
  AUTO_INCREMENT = 3
  DEFAULT CHARSET = utf8
  COLLATE = utf8_unicode_ci;

This is suboptimal for two reasons:

1. I can't run any queries that will return 1 row with both mobile and desktop medias' sources.
2. I have hard coded the media types into the columns in the ads table's columns

How might I better lay out my tables so that:

• An ad has only 1 of each type of media
• I can run 1 query to retrieve all medias of an ad
• (optional) The schema will allow for me to easily add new media types simply by adding a new row to the media_types table

Answer 1

You have the FOREIGN KEY relationships defined in the wrong direction. They should be the other way around, from medias to ads.

The added UNIQUE constraint on medias (ad_id, media_type_id) takes care of the restriction that an ad can have only one media, for every media_type.

This way, you can easily add more media types, just by inserting a new row in table media_types.

Updated tables (I've also slightly changed those id names to ad_id, media_id. Sorry but I just can't read the code otherwise):

ads

CREATE TABLE ads (
  ad_id  INT UNSIGNED NOT NULL AUTO_INCREMENT,
  CONSTRAINT ads__pk
    PRIMARY KEY (ad_id)
)
  ENGINE = InnoDB ;

medias

CREATE TABLE medias (
  ad_id          INT UNSIGNED                 NOT NULL
  media_id       INT UNSIGNED                 NOT NULL AUTO_INCREMENT,
  media_type_id  TINYINT UNSIGNED             NOT NULL,
  src            TEXT COLLATE utf8_unicode_ci NOT NULL,
  CONSTRAINT medias__pk
    PRIMARY KEY (media_id),
  CONSTRAINT medias__ad__media_type__uq
    UNIQUE ad_id__media_type_id__uqx  (ad_id, media_type_id),
  CONSTRAINT medias__ads__fk
    FOREIGN KEY (ad_id) 
    REFERENCES ads (ad_id),
  INDEX media_type_id__idx 
    (media_type_id),
  CONSTRAINT medias__media_types__fk
    FOREIGN KEY (media_type_id) 
    REFERENCES media_types (media_type_id)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8
  COLLATE = utf8_unicode_ci ;

media_types

CREATE TABLE media_types (
  media_type_id  TINYINT UNSIGNED        NOT NULL,
  created_at     TIMESTAMP               NULL     DEFAULT NULL,
  updated_at     TIMESTAMP               NULL     DEFAULT NULL,
  name           VARCHAR(255)
                 COLLATE utf8_unicode_ci NOT NULL,
  CONSTRAINT media_types__pk
    PRIMARY KEY (media_type_id),
  CONSTRAINT media_types__name__uq
    UNIQUE media_types__name__uqx  (name)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8
  COLLATE = utf8_unicode_ci ;