mysql - database design : User will submit a howto, 每个howto都会有一个或多个步骤关联，每个步骤可以有随机图片关联

Question

我正在尝试设计一个数据库，但我需要一些关系方面的帮助。我的表格设计是否正确？

这是数据库的想法..

用户将提交一份 howto，每个 howto 将有一个或多个关联的步骤(一对多)。每个步骤都可以有随机图片关联(另一对多)。所以我在想这个:

CREATE TABLE `HowtoStepImage`  
  `id` int(10) unsigned NOT NULL auto_increment,  
  `user_id` int(10) unsigned NOT NULL,  
  `howto_id` varchar(25) NOT NULL,  
  `step_id` varchar(25) NOT NULL,  
  `img_id` int(10) unsigned NOT NULL,  
  PRIMARY KEY  (`id`),  
  KEY `hsi_k_1` (`howto_id`),  
  CONSTRAINT `hsi_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`),  
  CONSTRAINT `hsi_ibfk_2` FOREIGN KEY (`step_id`) REFERENCES `HowtoStep` (`step_id`),  
  CONSTRAINT `hsi_ibfk_3` FOREIGN KEY (`img_id`) REFERENCES `StepImage` (`id`)  
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

table HowtoStep  
step_id, title, content, created  
primary key (step_id)

table StepImage  
img_id, filename, created


CREATE TABLE `UserHowtoComment` (  
  `id` int(10) unsigned NOT NULL auto_increment,  
  `howto_id` varchar(25) NOT NULL,  
  `user_id` int(10) unsigned NOT NULL,  
  `comment` varchar(500) NOT NULL,  
  `created` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,  
  PRIMARY KEY  (`id`),  
  KEY `UserHowtoComment_ibfk_1` (`howto_id`),  
  KEY `UserHowtoComment_ibfk_2` (`user_id`),  
  CONSTRAINT `UserHowtoComment_ibfk_1` FOREIGN KEY (`howto_id`) REFERENCES `HowtoStepImage` (`howto_id`),  
  CONSTRAINT `UserHowtoComment_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

但是，我在创建表时遇到错误，我确定这是由于我的数据库设计造成的。这是 mysql>SHOW ENGINE INNODB STATUS;显示:

091217  9:59:59 Error in foreign key constraint of table UserhowtoComment:
 FOREIGN KEY (`howto_id`) REFERENCES `howtoStepImage` (`howto_id`),
  CONSTRAINT `UserHowtoComment_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8:
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html
for correct foreign key definition.

howto_id 是 UserHowtoComment 中的一个键(索引)。我不确定这是否是这里的确切问题..

Answer 1

制作 3 个表格:一张用于 HowTo，一张用于 HowToStep，一张用于 HowToStepImage。

给每个表一个明确定义的键，例如一个数字或一个字符串。然后让“子”表引用父表的键。确保列也有清晰的名称。

表格操作指南
列 HowToId(键)

表格操作步骤
列 HowToStepId(key), HowToId

表格 HowToStepImage
列 HowToStepImageId(key), HowToStepId