Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
Thanks for taking the time to answer :) Thought no one would ever pass here :p Yes i do check for error codes. I'll use varchar(255) as you mentionned because i only want to store the filename of the image, not the whole path. –  Virus721 Dec 11 '12 at 16:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.