vHkSFT5K

1SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
2
3CREATE TABLE IF NOT EXISTS `associates` (
4  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
5  `name` varchar(64) NOT NULL,
6  `website` varchar(255) DEFAULT NULL,
7  PRIMARY KEY (`id`),
8  UNIQUE KEY `name` (`name`)
9) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;
10
11CREATE TABLE IF NOT EXISTS `associates_projects` (
12  `associate_id` tinyint(3) unsigned NOT NULL,
13  `project_id` smallint(5) unsigned NOT NULL,
14  PRIMARY KEY (`associate_id`,`project_id`),
15  KEY `fk_project` (`project_id`)
16) ENGINE=InnoDB DEFAULT CHARSET=utf8;
17
18CREATE TABLE IF NOT EXISTS `projects` (
19  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
20  `associate_id` tinyint(3) unsigned DEFAULT NULL,
21  `title` varchar(255) NOT NULL,
22  `slug` varchar(255) NOT NULL,
23  `completed` int(10) unsigned NOT NULL,
24  `website` varchar(255) DEFAULT NULL,
25  PRIMARY KEY (`id`),
26  UNIQUE KEY `uniq_slug` (`slug`),
27  KEY `fk_associate` (`associate_id`)
28) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=10 ;
29
30CREATE TABLE IF NOT EXISTS `project_images` (
31  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
32  `project_id` smallint(5) unsigned NOT NULL,
33  `file` varchar(255) NOT NULL,
34  `title` varchar(64) NOT NULL,
35  PRIMARY KEY (`id`),
36  KEY `fk_project` (`project_id`)
37) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=12 ;
38
39CREATE TABLE IF NOT EXISTS `users` (
40  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
41  `username` varchar(24) NOT NULL,
42  `password` varchar(40) DEFAULT NULL,
43  `last_login` int(10) unsigned DEFAULT NULL,
44  PRIMARY KEY (`id`),
45  UNIQUE KEY `uniq_username` (`username`),
46  KEY `idx_last_login` (`last_login`)
47) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;
48
49
50ALTER TABLE `associates_projects`
51  ADD CONSTRAINT `associates_projects_ibfk_1` FOREIGN KEY (`associate_id`) REFERENCES `associates` (`id`) ON DELETE CASCADE,
52  ADD CONSTRAINT `associates_projects_ibfk_2` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE;
53
54ALTER TABLE `projects`
55  ADD CONSTRAINT `projects_ibfk_1` FOREIGN KEY (`associate_id`) REFERENCES `associates` (`id`);
56
57ALTER TABLE `project_images`
58  ADD CONSTRAINT `project_images_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE;