This is my attempt at creating a database schema for a Minecraft logging plugin. I'm by no means a SQL expert, so suggestions would be greatly appreciated.
--
-- Table structure for table `actions`
--
DROP TABLE IF EXISTS `actions`;
CREATE TABLE IF NOT EXISTS `actions` (
`id` tinyint(3) unsigned NOT NULL,
`name` varchar(32) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- --------------------------------------------------------
--
-- Table structure for table `actors`
--
DROP TABLE IF EXISTS `actors`;
CREATE TABLE IF NOT EXISTS `actors` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(32) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- --------------------------------------------------------
--
-- Table structure for table `materials`
--
DROP TABLE IF EXISTS `materials`;
CREATE TABLE IF NOT EXISTS `materials` (
`id` smallint(5) unsigned NOT NULL,
`namespace` varchar(64) NOT NULL,
`name` varchar(128) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`,`namespace`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- --------------------------------------------------------
--
-- Table structure for table `world_blocks`
--
DROP TABLE IF EXISTS `world_blocks`;
CREATE TABLE IF NOT EXISTS `world_blocks` (
`id` int(10) unsigned NOT NULL,
`datetime` datetime NOT NULL,
`actionid` tinyint(3) unsigned NOT NULL,
`actorid` mediumint(8) unsigned NOT NULL,
`x` mediumint(9) NOT NULL,
`y` smallint(6) NOT NULL,
`z` mediumint(9) NOT NULL,
`blockid` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `datetime` (`datetime`),
KEY `actorid` (`actorid`),
KEY `coordinates` (`x`,`z`,`y`),
KEY `blockid` (`blockid`),
KEY `actionid` (`actionid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- --------------------------------------------------------
--
-- Table structure for table `world_entities`
--
DROP TABLE IF EXISTS `world_entities`;
CREATE TABLE IF NOT EXISTS `world_entities` (
`id` int(10) unsigned NOT NULL,
`datetime` datetime NOT NULL,
`actionid` tinyint(3) unsigned NOT NULL,
`actorid` mediumint(8) unsigned NOT NULL,
`x` mediumint(9) NOT NULL,
`y` smallint(6) NOT NULL,
`z` mediumint(9) NOT NULL,
`acteeid` mediumint(8) unsigned NOT NULL,
`metadata` text,
PRIMARY KEY (`id`),
KEY `datetime` (`datetime`),
KEY `actorid` (`actorid`),
KEY `coordinates` (`x`,`z`,`y`),
KEY `acteeid` (`acteeid`),
KEY `actionid` (`actionid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- --------------------------------------------------------
--
-- Table structure for table `world_items`
--
DROP TABLE IF EXISTS `world_items`;
CREATE TABLE IF NOT EXISTS `world_items` (
`id` int(10) unsigned NOT NULL,
`datetime` datetime NOT NULL,
`actionid` tinyint(3) unsigned NOT NULL,
`actorid` mediumint(8) unsigned NOT NULL,
`x` mediumint(9) NOT NULL,
`y` smallint(6) NOT NULL,
`z` mediumint(9) NOT NULL,
`itemid` smallint(5) unsigned NOT NULL,
`itemquantity` smallint(5) unsigned NOT NULL DEFAULT '1',
`metadata` text,
PRIMARY KEY (`id`),
KEY `datetime` (`datetime`),
KEY `actorid` (`actorid`),
KEY `coordinates` (`x`,`z`,`y`),
KEY `itemid` (`itemid`),
KEY `actionid` (`actionid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Constraints for dumped tables
--
--
-- Constraints for table `world_blocks`
--
ALTER TABLE `world_blocks`
ADD CONSTRAINT `world_blocks_ibfk_1` FOREIGN KEY (`actionid`) REFERENCES `actions` (`id`),
ADD CONSTRAINT `world_blocks_ibfk_2` FOREIGN KEY (`actorid`) REFERENCES `actors` (`id`),
ADD CONSTRAINT `world_blocks_ibfk_3` FOREIGN KEY (`blockid`) REFERENCES `materials` (`id`);
--
-- Constraints for table `world_entities`
--
ALTER TABLE `world_entities`
ADD CONSTRAINT `world_entities_ibfk_1` FOREIGN KEY (`actionid`) REFERENCES `actions` (`id`),
ADD CONSTRAINT `world_entities_ibfk_2` FOREIGN KEY (`actorid`) REFERENCES `actors` (`id`),
ADD CONSTRAINT `world_entities_ibfk_3` FOREIGN KEY (`acteeid`) REFERENCES `actors` (`id`);
--
-- Constraints for table `world_items`
--
ALTER TABLE `world_items`
ADD CONSTRAINT `world_items_ibfk_1` FOREIGN KEY (`actionid`) REFERENCES `actions` (`id`),
ADD CONSTRAINT `world_items_ibfk_2` FOREIGN KEY (`actorid`) REFERENCES `actors` (`id`),
ADD CONSTRAINT `world_items_ibfk_3` FOREIGN KEY (`itemid`) REFERENCES `materials` (`id`);
AUTO_INCREMENT=33
in the actors table seems odd. \$\endgroup\$