mysql中逗号分隔字段的更好替代方案

In my application whenever a user upload a wallpaper,i need to crop that wallpaper into 3 different sizes and store all those paths(3 paths for cropped images and 1 for original upload wallpaper) into my database.
I also need to store the tinyurl of the original wallpaper(one which is uploaded by user).

在我的应用程序中,每当用户上传壁纸时,我需要将该壁纸裁剪成3种不同的尺寸,并将所有这些路径(裁剪图像的3条路径和原始上传壁纸的1条路径)存储到我的数据库中。我还需要存储原始壁纸(由用户上传的一个)的tinyurl。

While solving the above described problem i come up with following table structure.

在解决上述问题的同时,我提出了以下表格结构。

CREATE TABLE `wallpapermaster` (
  `wallpaperid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `userid` bigint(20) NOT NULL,
  `wallpaperloc` varchar(100) NOT NULL,
  `wallpapertitle` varchar(50) NOT NULL,
  `wallpaperstatus` tinyint(4) DEFAULT '0' COMMENT '0-Waiting,1-approved,2-disapproved',
  `tinyurl` varchar(40) NOT NULL
) ENGINE=MyISAM

wallpaperloc is a comma separated field consisting of original wallpaper location plus locations of all cropped instances.

wallpaperloc是一个逗号分隔的字段,由原始壁纸位置和所有裁剪实例的位置组成。

I know using comma separated field considered to be a bad design in the world of relational database,So Would you like to suggest some other neat and efficient ways?

我知道在关系数据库的世界中使用逗号分隔的字段被认为是一个糟糕的设计,所以你想建议一些其他整洁有效的方法吗?

5 个解决方案

#1

Use a 1:n relationship between the wallpapermaster and a location table.

在wallpapermaster和位置表之间使用1:n关系。

Something like this:

像这样的东西:

CREATE TABLE wallpapermaster (
  wallpaperid     int unsigned NOT NULL AUTO_INCREMENT,
  userid          bigint NOT NULL,
  wallpaperloc    varchar(100) NOT NULL,
  wallpapertitle  varchar(50) NOT NULL,
  wallpaperstatus tinyint DEFAULT '0' COMMENT '0-Waiting,1-approved,2-disapproved',
  primary key (wallpaperid)
) ENGINE=InnoDB;


CREATE TABLE wallpaperlocation (
  wallpaperid  int unsigned NOT NULL,
  location     varchar(100) NOT NULL,
  tinyurl      varchar(40),
  constraint fk_loc_wp 
      foreign key (wallpaperid) 
      references wallpapermaster (wallpaperid),
   primary key (wallpaperid, location)
) ENGINE=InnoDB;

The primary key in wallpaperlocation ensures that the same location cannot be inserted twice.

wallpaperlocation中的主键确保无法插入相同的位置两次。

Note that int(10) does not define any datatype constraints. It is merely a hint for client application to indicate how many digits the number has.

请注意,int(10)不定义任何数据类型约束。它仅仅是客户端应用程序的提示,指示该数字具有多少位数。

5 个解决方案

#1

更多相关文章

随机推荐