database design question

Question

Hello, I am trying to design a database but I need some help with the relationships. Am i getting the table design right?

Here is the database idea..

User will submit a howto, each howto will have one or more steps associated with(a one to many). each step can have random pictures associated with(another one to many). so I am thinking of this:

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;

however, I am getting error when creating the table, I am sure it is due to my database design. here is what mysql>SHOW ENGINE INNODB STATUS; shows:

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.

the howto_id is a key(index) in UserHowtoComment though. I am not sure if that is the exact problem here..

Answer 1

your query is really messy e.g. step_id varchar(25) needs to be an int.

why dont you just use a gui programm or maybe the good old phpMyAdmin, so you can learn the from the Querys they are creating, phpMyAdmin also has a advanced feature call "Designer" to create constraints.

Answer 2

If I read this correctly, your HowToComment id is a foreign key to HowtoStepImage. Does every comment have to have an image? Seems like a chicken and the egg issue. It seems, from your problem description, that an image links to a comment, not the other way around.

Answer 3

you're falling prey to the misleading terminology in MySQL. in the relational model, key is (necessarily) distinct. in the MySQL-speak, it's just an index. you need either PRIMARY KEY or UNIQUE KEY.

edit to add explicitly what is implied above: foreign keys must point to a key in the relational sense.

Answer 4

Make 3 tables: one for HowTo, one for HowToStep, one for HowToStepImage.

Give each table a clearly defined key, e.g. a number or a string. Then let the 'child' table refer to the key of the parent table. Make sure that the columns have clear names as well.

TABLE HowTo
COLUMNS HowToId(key)

TABLE HowToStep
COLUMNS HowToStepId(key), HowToId

TABLE HowToStepImage
COLUMNS HowToStepImageId(key), HowToStepId