Despot:Schema2

Proposed Schema

`repositories`

 `id` mediumint(8) unsigned NOT NULL auto_increment,
 `name` varchar(255) NOT NULL,
 `type` tinyint(3) unsigned NOT NULL,
 `root` varchar(255) NOT NULL,
 `vouchers_req` tinyint(3) unsigned NOT NULL default '2',
 `sr_req` tinyint(3) unsigned NOT NULL default '1',
 `private` tinyint(1) NOT NULL default '0',
 PRIMARY KEY  (`id`),
 UNIQUE KEY `name` (`name`,`type`)
 ENGINE=InnoDB DEFAULT CHARSET=utf8

`partitions`

 `id` mediumint(8) unsigned NOT NULL auto_increment,
 `name` varchar(255) NOT NULL,
 `state` enum('open','restricted','closed') NOT NULL default 'open',
 `description` text,
 `doclinks` mediumtext,
 `newsgroups` mediumtext,
 `private` tinyint(1) NOT NULL default '0',
 PRIMARY KEY  (`id`),
 KEY `name` (`name`),
 KEY `parent_id` (`parent_id`)
 ENGINE=InnoDB DEFAULT CHARSET=utf8

`repositories_partitions`

 `repository_id` mediumint(8) unsigned NOT NULL,
 `partition_id` mediumint(8) unsigned NOT NULL
 KEY `repository_id` (`repository_id`),
 KEY `partition_id` (`partition_id`)
 ENGINE=InnoDB DEFAULT CHARSET=utf8

`branches`

 `id` mediumint(8) unsigned NOT NULL auto_increment,
 `name` varchar(255) NOT NULL,
 `description` varchar(255) NULL,
 PRIMARY KEY  (`id`)
 ENGINE=InnoDB DEFAULT CHARSET=utf8

`files`

 `partition_id` mediumint(8) unsigned NOT NULL,
 `repository_id` mediumint(8) unsigned NOT NULL,
 `branch_id` mediumint(8) unsigned NOT NULL default '0',
 `pattern` varchar(255) NOT NULL,
 KEY `partition_id` (`partition_id`),
 KEY `repository_id` (`repository_id`),
 KEY `branch_id` (`branch_id`)
 ENGINE=InnoDB DEFAULT CHARSET=utf8

`members`

 `user_id` mediumint(8) unsigned NOT NULL,
 `partition_id` mediumint(8) unsigned NOT NULL,
 `class` enum('owner','peer','member') NOT NULL default 'member',
 UNIQUE KEY `user_id` (`user_id`,`partition_id`),
 KEY `class` (`class`)
 ENGINE=InnoDB DEFAULT CHARSET=utf8

`logs` (incomplete)

 `id` int(11) unsigned NOT NULL auto_increment,
 `user_id` mediumint(8) unsigned NOT NULL,
 `action` varchar(50) NOT NULL,
 `field` varchar(30) NULL,
 `old_val` text,
 `new_val` text,
 `notes` text NULL,
 `time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00'
 PRIMARY KEY (`id`)
 ENGINE=InnoDB DEFAULT CHARSET=utf8

`restrictions`

 `user_id` mediumint(8) unsigned NOT NULL,
 `partition_id` mediumint(8) unsigned NOT NULL,
 UNIQUE KEY `user_id` (`user_id`,`partition_id`)
 ENGINE=InnoDB DEFAULT CHARSET=utf8

`sessions`

 `id` varchar(255) NOT NULL default '',
 `data` text,
 `expires` int(11) default NULL,
 PRIMARY KEY  (`id`)
 ENGINE=InnoDB DEFAULT CHARSET=utf8

`syncneeded`

 `repository_id` mediumint(8) unsigned NOT NULL,
 `needed` tinyint(1) NOT NULL default '0'
 ENGINE=InnoDB DEFAULT CHARSET=utf8

`tags` (incomplete)

 `id` mediumint(8) unsigned NOT NULL auto_increment,
 `name` varchar(255) NOT NULL, 
 PRIMARY KEY `id`
 ENGINE=InnoDB DEFAULT CHARSET=utf8

`vouchers`

 `user_id` mediumint(8) unsigned NOT NULL,
 `partition_id` mediumint(8) unsigned NOT NULL,
 `voucher_id` mediumint(8) unsigned NOT NULL,
 `sr` tinyint(1) NOT NULL default '0',
 KEY `user_id` (`user_id`,`partition_id`,`voucher_id`)
 ENGINE=InnoDB DEFAULT CHARSET=utf8

Notes

• Make tags object agnostic (i.e. allow tagging users, partitions, etc. together with a single tag).
• Consider using InnoDB for several tables where ACID compliance is important.
  • Also allows simplification of table relationships with real foreign keys and constraints.
• Replace *_changes tables with a unified and more comprehensive generic log table.
  • Decide which log items should expire over time.