I'm thinking how to store images into my database. I have 2 "macro-entities", users and animals.
我正在考虑如何将图像存储到我的数据库中。我有2个“宏观实体”,用户和动物。
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:
最简单的解决方案是创建一个交叉表users_img和另一个交叉表animal_img,但它似乎并不优雅。像这样:
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?
我应该创建另外两个交叉表“ALBUMS_USERS”和“ALBUMS_ANIMALS”......这是不可维护的!我无法为2个或更多“宏实体”创建一个简单,优雅和优雅的数据库。怎么办?什么是最好的方式?
The queries will be like:
查询将如下:
- without albums: "select all images where idUsers/idAnimals = X". When I visits the user's (or his animal) page, in a section I would like to see the thumbnails of his images.
- with albums: "select all albums where idUsers/idAnimals = X". When I visits the user's page, I would like to see the thumbnails of his albums.
没有专辑:“选择idUsers / idAnimals = X的所有图像”。当我访问用户(或他的动物)页面时,在一个部分我想看到他的图像的缩略图。
与专辑:“选择idUsers / idAnimals = X的所有专辑”。当我访问用户的页面时,我想看到他的专辑的缩略图。
I'll use amazon s3 for storing images and albums. the routes of images are:
我将使用amazon s3来存储图像和专辑。图像的路线是:
1 - Without albums
1 - 没有专辑
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)
例如:my-bucket-app / user-1 / img / 1_102020304-400.jpg(400是图像长边的长度)
2 - With albums
2 - 有专辑
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)
例如:my-bucket-app / user-1 / albums-1 / 1_102020304-400.jpg(400是图像长边的长度)
the filenames are generated by this function in php:
文件名由php中的此函数生成:
$userID . _ . md5(microtime());
I'll store into a bucket, 4 images of every upladed images. ex:
我将存储在一个桶中,每张上传图像的4张图像。例如:
1_102020304-400.jpg for the thumbnails
1_102020304-400.jpg缩略图
1_102020304-2048.jpg for the large-images size
1_102020304-2048.jpg为大图像尺寸
1_102020304-1024.jpg for the medium-images size
1_102020304-1024.jpg适用于中等图像尺寸
1_102020304-original.jpg for the original images
1_102020304-original.jpg原始图像
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.
正是为什么我认为只将图像名称1_102020304-400.jpg的这一部分存储到“文件名”字段中,并将一个函数添加大小和mimetype(这被记忆到另一个字段“mime_type”)。
1 个解决方案
#1
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.
不要将琐碎的东西正常化;使用CHAR(1)CHARACTER SET ascii作为性别。有标准的2个字母的国家/地区代码。
name varchar(255)
Don't get carried away with VARCHAR lengths.
不要被VARCHAR长度带走。
You have not mentioned the ENGINE -- use InnoDB.
您还没有提到ENGINE - 使用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.
您预计超过40亿(INT UNSIGNED的限制)? INT是4个字节; BIGINT是8.更小更好。
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.
一个简单的ENUM就足够了。
You can't finalize a schema without specifying what the queries will be like.
您无法在不指定查询内容的情况下最终确定架构。
#1
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.
不要将琐碎的东西正常化;使用CHAR(1)CHARACTER SET ascii作为性别。有标准的2个字母的国家/地区代码。
name varchar(255)
Don't get carried away with VARCHAR lengths.
不要被VARCHAR长度带走。
You have not mentioned the ENGINE -- use InnoDB.
您还没有提到ENGINE - 使用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.
您预计超过40亿(INT UNSIGNED的限制)? INT是4个字节; BIGINT是8.更小更好。
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.
一个简单的ENUM就足够了。
You can't finalize a schema without specifying what the queries will be like.
您无法在不指定查询内容的情况下最终确定架构。