CODEWITHSUNDEEP
mysqldatabase-designdatabase-schema
Marco
Marco

Reputation: 2737

Image table schema feedback

Ok, so I’ve been dealing with this for the last couple of months, mostly because I’m a newbie... I did my reading and my research and this is the result...

I’m designing the model for an image table for a gallery for my website. Basically, I will upload an image (filename) and create 5 different sizes (small, thumb, medium, large, big).

I’ve already set up the guidelines for the different sizes:

Each size will have it's own filename defined by the filename + a suffix representing the size. So, a small image filename will end up with a _ssuffix (1234_s.jpg), thumbs with a _tsuffix (1234_t.jpg) and so on...

I’ve also decide it to create a small file system structure since I will probably end up uploading around 125 000 images. Something similar to this:

http://www.mywebsite.com/gallery/images/0/6/image1.png
http://www.mywebsite.com/gallery/images/5/4/image2.png

The path to the images will reside on a config.php file and I will keep the relative path to the image folder in the database under a column named relative_dir

I also need the keep the width and the height so it displays ok in the browser (using getimagesize will probably put to much strain on the server if there is many thumbs to display)

So, after a couple of weeks of reading and researching, i came up with this (remember i'm a newbie)

Any feedback, suggestions?

IMAGE
-----------------------
image_id
title
caption
relative_dir
filename
small_filename
small_width
small_height
thumb_filename
thumb_width
thumb_height
medium_filename
medium_width
medium_height
large_filename
large_width
large_height
big_filename
big_width
big_height

Upvotes: 2

Views: 1238

Answers (1)

Explosion Pills
Explosion Pills

Reputation: 191749

Having columns like value1, value2, value3 or in your case small, medium, large is indicative of a denormalization problem. It makes for a bulky table, and you will have to change the schema should you ever want to add (or remove) an image size. I'm not sure why you need the _width/_height columns since each seems like it has a static width/height based on its size status (e.g. small is always 100px), but no matter. I suggest three tables:

ImageUploads -- canonical image table
iuID, title, caption, relative_dir, filename
(do you need *both* relative_dir/filename)

ImageSizes -- settings for sizes; width/height may go here as well
isID,
sizeName (small, medium, large, big, thumb)
sizeSuffix (_s, _m, _l, _b, _t?)

Images
iuID, isID

Using the Images table you can easily get the canonical image filename, title/caption/etc. and get the suffix you need. Width/height will either go on this table if it needs to differ per each individual file, or on ImageSizes if it's per-size. You could also use both if you wanted to have defaults but allow for flexibility.

Upvotes: 2

Related Questions