關係混亂

假設我有一張桌子ads，每個廣告都有幾種媒體 - 放在一張medias桌子上。每個媒體都可以是幾種類型中的一種。類型在media_types表格中。

例如，一個廣告有兩種媒體：一種是桌麵類型，一種是移動類型。

我需要該ads表來引用 中的多行medias，但任何給定類型只有 1 行。我還需要能夠輕鬆增長的類型數量。

編輯的架構：

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

緊身衣

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

媒體類型

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;

這是次優的，原因有兩個：

1. 我無法執行任何將返回 1 行的查詢，其中包含移動和桌面媒體的源。
2. 我已將媒體類型硬編碼到表格列中ads的列中

我怎樣才能更好地佈置我的表格，以便：

• 一個廣告只有一種媒體類型
• 我可以執行 1 個查詢來檢索廣告的所有媒體
• media_types（可選）架構將允許我通過向表中添加新行來輕鬆添加新的媒體類型

您FOREIGN KEY在錯誤的方向上定義了關係。它們應該是相反的，從medias到ads。

添加的UNIQUE約束medias (ad_id, media_type_id)處理了 anad只能有一個的限制media，對於每個media_type。

這樣，您只需在 table 中插入新行，即可輕鬆添加更多媒體類型media_types。

更新的表格（我也將這些id名稱稍微更改為ad_id, media_id。抱歉，否則我無法閱讀程式碼）：

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 ;

引用自：https://dba.stackexchange.com/questions/134482