I want to store image (details) in my database, and want for each image the possibility to have from 1 to N versions.
I want a single file (src) to be associated with a single version. There must not be more than one version for an single image with the same dimensions and format (no point having a version with 1920x1080 jpg and 1920x1080 jpg, whereas 1920x1080 jpg and 1920x1080 png would be ok).
I have come out with the following tables :
-- ---------------------------------------------------------------------------------
-- IMAGE TABLE
-- ---------------------------------------------------------------------------------
DROP TABLE IF EXISTS "image_e";
CREATE TABLE IF NOT EXISTS "image_e"
(
"id" int unsigned NOT NULL AUTO_INCREMENT,
"title" varchar(32) NOT NULL,
"alt" varchar(16) NOT NULL,
PRIMARY KEY ("id")
) ENGINE = MyISAM DEFAULT CHARSET = utf8 COLLATE = utf8_bin;
-- ---------------------------------------------------------------------------------
-- IMAGE VERSION TABLE
-- ---------------------------------------------------------------------------------
DROP TABLE IF EXISTS "image_version_e";
CREATE TABLE IF NOT EXISTS "image_version_e"
(
"id" int unsigned NOT NULL AUTO_INCREMENT,
"imageId" int unsigned NOT NULL,
"format" varchar(4) NOT NULL,
"size" int unsigned NOT NULL,
"width" int unsigned NOT NULL,
"height" int unsigned NOT NULL,
"src" varchar(64) NOT NULL,
PRIMARY KEY ("id")
) ENGINE = MyISAM DEFAULT CHARSET = utf8 COLLATE = utf8_bin;
ALTER TABLE "image_version_e" ADD UNIQUE KEY ("imageId", "format", "width", "height");
ALTER TABLE "image_version_e" ADD UNIQUE KEY ("src");
image_version_e.imageId references image_e.id
This version is MyISAM, i will make a second version for InnoDB with this foreign key.
I don't care about insert / update / delete speed, but i do care about select speed as i will use this for a wallpaper gallery with a search function.
Are my unique keys done correctly ? Isn't it too much or not enough ? Is the schema well thought ? Is there a way to optimize it ?
Thanks for reading and for your help :)