Mysql – Relationships mess

database-designforeign keyMySQLschema

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

Best Answer

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 ;
Related Question