Mysql IMAGE Table Design

Question

I am a newbie designing a database and I need a help about picture table.

CREATE TABLE LANGUAGE (
    id int(10) unsigned not null auto_increment,
    LANGUAGE VARCHAR(45),
    IMAGE_id INT(10) UNSIGNED NOT NULL,
    // some more
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE PRODUCT(
    id int(10) unsigned not null auto_increment,
    // some more
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE PRODUCT_IMAGE(
        PRODUCT_id INT(10) UNSIGNED NOT NULL,
        IMAGE_id INT(10) UNSIGNED NOT NULL
    )ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE CATEGORY(
        id int(10) unsigned not null auto_increment,
        IMAGE_id INT(10) UNSIGNED NOT NULL,
        // some more
    )ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

I want to create a image table but i did not figure out a right way yet.

one product may have much more that one image
every category and language have one image
I want a elastic image table that i can use it in website and apps
I might need a different sizes of an image

so i designed something like that

CREATE TABLE IMAGE(
    id int(10) unsigned not null auto_increment,
    extension varchar(5),  
    file_name varchar(45),
    file LONGBLOB,
    //some more
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Should i keep some certain sizes of an image I then resize the image in my apps or website?

bicanul123 · Answer 1 · 2018-07-17T12:11:46.137

It's not so safety to put images into database, I don't understand why u want to do this...

create table imagetest (
    image_id        tinyint(3)  not null default '0',
    image_type      varchar(25) not null default '',
    image           blob        not null,
    image_size      varchar(25) not null default '',
    image_ctgy      varchar(25) not null default '',
    image_name      varchar(50) not null default ''
);
Then you can write an image to the database like:
$imgData = file_get_contents($filename);
$size = getimagesize($filename);
mysql_connect("localhost", "$username", "$password");
mysql_select_db ("$dbname");
$sql = "INSERT INTO testblob
    ( image_id , image_type ,image, image_size, image_name)
    VALUES
    ('', '{$size['mime']}', '{$imgData}', '{$size[3]}', 
     '{$_FILES['userfile']['name']}')";
mysql_query($sql);
You can display an image from the database in a web page with:
$link = mysql_connect("localhost", "username", "password");
mysql_select_db("testblob");
$sql = "SELECT image FROM testblob WHERE image_id=0";
$result = mysql_query("$sql");
header("Content-type: image/jpeg");
echo mysql_result($result, 0);
mysql_close($link);

Thanks for you comment, why do you think it is not safety to put images into databes? — Kai Tera, Jul 17 '18 at 12:32
You're welcome! Well, because it requires additional code to extract and stream the images, Latency may be slower than direct file access, the database server it's loaded very heavily and it's not worth it... I would store the path instead, but if u want to do this the easiest and the most convenient way it's to name the images according to the primary key, so it's easy to find the image that belongs to a particular record, and vice versa. But at the same time you're not storing anything about the image in the database. — bicanul123, Jul 17 '18 at 12:45

score 0 · Answer 2 · answered Jul 17 '18 at 12:10

May I suggest you stay away from putting images into the database and instead store file path only. I will not go to length explaining it as its all been said before better than I possibly could here.

I would like you to consider this answer: Storing Images in DB - Yea or Nay?

and possibly read this lovely article by James Hamilton: https://perspectives.mvdirona.com/2008/06/facebook-needle-in-a-haystack-efficient-storage-of-billions-of-photos/

In addition this article really helped me as it explains how flickr.com scaled. http://highscalability.com/flickr-architecture

I know its not good practise to throw links up without capturing the essence of the links here. But the essence boils down to this: Do not store images in the db.

You could consider this instead for your image table:

CREATE TABLE IMAGE(
    id int(10) unsigned not null auto_increment,
    image_location varchar(255),
    //some more
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

I left this as a varchar but you might need to change this to some form of text format if your path is ever going to be more than 255 long or if you have characters in your path that will be multibyte encoded as this will subtract from the 255 as well.

Thanks for your answer. Actually i have searched about where to store my imgaes, in blob or folders and they both have positive sides and negative sides but i just want to avoid folder because when i need to change something in images, I do not wanna deal with folder system. — Kai Tera, Jul 17 '18 at 12:25

Mysql IMAGE Table Design

2 Answers2