Despot:Schema2
From MozillaWiki
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</span>, `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.