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.idThis 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 :)
Are these tables concepted correctly?
mysql;sql
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.