CODEWITHSUNDEEP
mysqldatabasedatabase-designdatabase-schema
Alessandro Corradini
Alessandro Corradini

Reputation: 499

How can I improve my database for storing images?

I'm thinking how to store images into my database. I have 2 "macro-entities", users and animals.

USERS

idUsers bigint(20) AI PK 
name varchar(255) 
surname varchar(255) 
birthdate date 
email varchar(250) 
psw varchar(128) 
tk int(11) 
created_time datetime
mod_time datetime  
idCategory tinyint(4) FK
idCountry tinyint(4) FK
idGender tinyint(4) FK
description text

ANIMALS

idAnimals bigint(20) AI PK 
name varchar(255) 
idGender tinyint(4) FK
idUsers bigint(20) FK
birthdate date 
visits int(11) 
identification varchar(200) 
idBreed smallint(6) FK
created_time datetime
mod_time datetime 
description text

IMG

idImg bigint(20) PK 
filename text 
created_time datetime 
mime_type varchar(50)

The easiest solution is create a cross table users_img and another cross table animal_img, but It does not seem very elegant. Like so:

IMG_USERS

idImg bigint(20) PK FK
idUsers bigint(20) PK FK

IMG_ANIMALS

idImg bigint(20) PK FK
idAnimals bigint(20) PK FK

It would work well, but if I want to give the possibility to create albums in the future, I can't do it.

For albums it's simple:

ALBUMS

idAlbums bigint(20) AI PK 
name varchar(255) 
descriptions text 
created_time datetime 
modified_time datetime 
slug varchar(255)

IMG_ALBUMS

idImg bigint(20) PK FK
idAlbum bigint(20) PK FK

ENGINE InnoDB

I should create 2 another cross table "ALBUMS_USERS" and "ALBUMS_ANIMALS"...It is not maintainable! I can't create a simple, good and elegant database for 2 or more "macro-entities". How can do it? What's the best way?

The queries will be like:

I'll use amazon s3 for storing images and albums. the routes of images are:

1 - Without albums

my-bucket-app/[user|animal]-[idUsers|animals]/img/[filename]-[width-of-image].[extension]

ex: my-bucket-app/user-1/img/1_102020304-400.jpg (400 is the lenght of long side of the image)

2 - With albums

my-bucket-app/[user|animals]-[idUsers|animals]/album-[idAlbum]/[filename]-[width-of-image].[extension]

ex: my-bucket-app/user-1/albums-1/1_102020304-400.jpg (400 is the lenght of long side of the image)

the filenames are generated by this function in php:

$userID . _ . md5(microtime());

I'll store into a bucket, 4 images of every upladed images. ex:

1_102020304-400.jpg for the thumbnails

1_102020304-2048.jpg for the large-images size

1_102020304-1024.jpg for the medium-images size

1_102020304-original.jpg for the original images

Precisely why I thought to store only this part of image's name 1_102020304-400.jpg into "filename" field and adding size and mimetype (this is memorized into another field "mime_type") with a function.

Upvotes: 1

Views: 181

Answers (1)

Rick James
Rick James

Reputation: 142518

idGender tinyint(4) FK
idCountry tinyint(4) FK

Don't normalize trivial things; use CHAR(1) CHARACTER SET ascii for gender. There are standard 2-letter country codes.

name varchar(255)

Don't get carried away with VARCHAR lengths.

You have not mentioned the ENGINE -- use InnoDB.

idUsers bigint(20)

You are expecting more than 4 billion (the limit of INT UNSIGNED)? INT is 4 bytes; BIGINT is 8. Smaller is better.

In your many-to-many mappings (which are necessary), you are likely to need an index going the opposite direction.

mime_type varchar(50)

A simple ENUM should suffice.

You can't finalize a schema without specifying what the queries will be like.

Upvotes: 1

Related Questions