1
\$\begingroup\$

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

\$\endgroup\$

1 Answer 1

1
\$\begingroup\$

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.

\$\endgroup\$
1
  • \$\begingroup\$ 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. \$\endgroup\$
    – Virus721
    Dec 11, 2012 at 16:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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