php-file manager db design (mysql)

Info about images (width/height) are irrelevant. If i need that i'll put it in another table but i don't need images info. What do you think about this d开发者_如何学JAVAb design? What do you think about MyISAM vs InnoDB for specific tables?

Thank you, I appreciate any feedback.

DROP TABLE IF EXISTS `directory`;
CREATE TABLE `directory` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `parent_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

DROP TABLE IF EXISTS `file`;
CREATE TABLE `file` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `fk_directory_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

DROP TABLE IF EXISTS `tag`;
CREATE TABLE `tag` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

DROP TABLE IF EXISTS `tags_files`;
CREATE TABLE `tags_files` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fk_file_id` int(11) DEFAULT NULL,
  `fk_tag_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

---

• column name should be unique

• and it should not using utf8_unicide_ci for this column, due to case sensitives,

for example, in linux (not in window)

example.jpg != example.JPG

using ci will cause the unique name problem

• if you require lots of write, innodb should be more appropriate

• other images additional info like, width, height can be stored into table in order to facility search/filter

• unlikely you would like to keep versions of the file, but you should store the information about creator (like user_id), so, you can trace back information easily

---

Since only "ajreal" answered this question i decided that base for my db design will be db design i suggested. Thanks ajreal for the answer(voteup:)). I know, ajreal, about storing all additional info(users, rights, acl, image sizes, ... and all the rest "small infos" that should be stored:) i just wanted to hear another opinions). Thanks.