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:
- I can't run any queries that will return 1 row with both mobile and desktop medias' sources.
- 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
ads.media_desktop_id
andads.media_mobile_id
, as well as theKEY
s on those columns (you should create normal indexes on them instead) then you'll no longer have the restriction you say. Also, what do you mean by retrieving all medias of an ad? Wouldn't you rather the other way around, i.e. all ads for a given media?