Database schema for a Minecraft logging plugin

Question

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`);

• ChrisWue: The AUTO_INCREMENT=33 in the actors table seems odd.
• Andy Shulman: I just exported my table schema. I had some data in there, apparently 32 rows. Fixed :)

Answer 1

Your Table Declarations are a little redundant with the DROP TABLE if it exists and then CREATE TABLE if it doesn't exist.

The SQL Engine is going to do an extra search to see if the table exists when we already know that if it existed it was dropped.

DROP TABLE IF EXISTS `actors`;
CREATE TABLE IF NOT EXISTS `actors` (...

If you have a large Table the Drop is going to take some time in itself. If you have a large Database it is going to take at least a little bit of time looking for a table that we already know doesn't exist.

I would just change it to

DROP TABLE IF EXISTS 'actors';
CREATE TABLE 'actors';

I know this is just to set up the Database itself. but that was the only thing that sort of bugged me about your code

with your NOT NULL's in your tables world_blocks and world_entities and world_items for the coordinates, I don't play Minecraft (please don't judge) but I imagine that you have to create a block in your inventory before adding it to the world. so you would have to have some coordinates attached to it if you are going to add it to the log before you actually add it to the world. (I hope that makes sense)