Are these tables concepted correctly?

Question

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 :)

Answer 1

The unique keys will enforce the requirements you specified, but unless your data entry program either traps error codes returned by MySQL and prompts the user for correction or checks for uniqueness before saving the record, you will probably experience a lot of record rejections where you won't understand why.

I would also use a much larger VARCHAR maximum length for the image_version_e.src column. Remember that VARCHAR columns only require as much storage space as needed for the actual data with trailing spaces stripped (usually). VARCHAR(255) or more is not unreasonable as it will accommodate those really long directory paths or URLs. Your data entry form field for it should be scrollable but much shorter... probably a multiline text field.

See more details in the MySQL documentation of CHAR and VARCHAR.