MySQL Database Design

[Karlos94]

Admittedly I am no good with designing and creating MySQL database, however I have had a go today and I was wondering if someone could tell me what I am doing wrong/right or how I could do better! this would be much appreciated!

P.s I know about the long, tedious insert queries at the bottom, they were generated. Not my fault ;)

[mysql]SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;

-- -----------------------------------------------------

-- Table `mydb`.`group`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `mydb`.`group` (

`group_id` INT(8) UNSIGNED NOT NULL AUTO_INCREMENT ,

`group_title` VARCHAR(20) NOT NULL ,

`group_role` VARCHAR(45) NOT NULL ,

PRIMARY KEY (`group_id`) )

ENGINE = InnoDB;

 

-- -----------------------------------------------------

-- Table `mydb`.`houses`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `mydb`.`houses` (

`house_id` INT(8) UNSIGNED NOT NULL AUTO_INCREMENT ,

`house_name` VARCHAR(20) NOT NULL ,

`house_desc` VARCHAR(45) NULL DEFAULT NULL ,

`house_buy_price` INT(10) UNSIGNED NOT NULL DEFAULT 0 ,

`house_sale_price` INT(10) UNSIGNED NOT NULL ,

PRIMARY KEY (`house_id`) )

ENGINE = InnoDB;

 

-- -----------------------------------------------------

-- Table `mydb`.`users_meta`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `mydb`.`users_meta` (

`user_id` INT(8) UNSIGNED NOT NULL ,

`house_id` INT(8) UNSIGNED NOT NULL DEFAULT 1 ,

PRIMARY KEY (`user_id`) ,

INDEX `fk_users_houses` (`house_id` ASC) ,

INDEX `fk_users_info` (`user_id` ASC) ,

CONSTRAINT `fk_users_houses`

FOREIGN KEY (`house_id` )

REFERENCES `mydb`.`houses` (`house_id` )

ON DELETE NO ACTION

ON UPDATE NO ACTION,

CONSTRAINT `fk_users_info`

FOREIGN KEY (`user_id` )

REFERENCES `mydb`.`users` (`user_id` )

ON DELETE CASCADE

ON UPDATE CASCADE)

ENGINE = InnoDB;

 

-- -----------------------------------------------------

-- Table `mydb`.`users`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `mydb`.`users` (

`user_id` INT(8) UNSIGNED NOT NULL AUTO_INCREMENT ,

`group_id` INT UNSIGNED NOT NULL DEFAULT 2 ,

`user_name` VARCHAR(20) NOT NULL ,

`user_pass` CHAR(64) NOT NULL ,

`user_ip_addr` INT(10) NOT NULL ,

`user_email` VARCHAR(45) NOT NULL ,

`user_active` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0 ,

`user_active_key` CHAR(32) NULL ,

`user_forgot_pass_key` CHAR(32) NULL ,

`user_registered` INT(11) UNSIGNED NOT NULL ,

`user_last_login` INT(11) UNSIGNED NOT NULL ,

PRIMARY KEY (`user_id`) ,

INDEX `fk_users_group` (`group_id` ASC) ,

CONSTRAINT `fk_users_group`

FOREIGN KEY (`group_id` )

REFERENCES `mydb`.`group` (`group_id` )

ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = InnoDB;

 

-- -----------------------------------------------------

-- Table `mydb`.`intel`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `mydb`.`intel` (

`intel_id` INT(8) UNSIGNED NOT NULL AUTO_INCREMENT ,

`intel_posted_by` INT(8) UNSIGNED NOT NULL DEFAULT 0 ,

`intel_title` VARCHAR(25) NOT NULL ,

`intel_text` TEXT NOT NULL ,

`intel_severity` ENUM('Low', 'Normal', 'High') NOT NULL DEFAULT 'Normal' ,

`intel_subject` ENUM('Competition', 'General News', 'Update', 'Warning') NOT NULL DEFAULT 'General News' ,

PRIMARY KEY (`intel_id`) ,

INDEX `fk_intel_users` (`intel_posted_by` ASC) ,

CONSTRAINT `fk_intel_users`

FOREIGN KEY (`intel_posted_by` )

REFERENCES `mydb`.`users` (`user_id` )

ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = InnoDB;

 

SET SQL_MODE=@OLD_SQL_MODE;

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

-- -----------------------------------------------------

-- Data for table `mydb`.`group`

-- -----------------------------------------------------

BEGIN;

INSERT INTO `mydb`.`group` (`group_id`, `group_title`, `group_role`) VALUES (NULL, 'NPC', 'Non-Playing Computer');

INSERT INTO `mydb`.`group` (`group_id`, `group_title`, `group_role`) VALUES (NULL, 'Member', 'General Player');

INSERT INTO `mydb`.`group` (`group_id`, `group_title`, `group_role`) VALUES (NULL, 'Player Enquires', 'Staff (Helpers)');

INSERT INTO `mydb`.`group` (`group_id`, `group_title`, `group_role`) VALUES (NULL, 'Forum Mods', 'Staff (Forum Moderation Team)');

INSERT INTO `mydb`.`group` (`group_id`, `group_title`, `group_role`) VALUES (NULL, 'General Mods', 'Staff (General Moderation Team)');

INSERT INTO `mydb`.`group` (`group_id`, `group_title`, `group_role`) VALUES (NULL, 'Admins', 'Staff (Game Development Team)');

INSERT INTO `mydb`.`group` (`group_id`, `group_title`, `group_role`) VALUES (NULL, 'Game Owner', 'Staff (Game Owner/Founder)');

COMMIT;

-- -----------------------------------------------------

-- Data for table `mydb`.`houses`

-- -----------------------------------------------------

BEGIN;

INSERT INTO `mydb`.`houses` (`house_id`, `house_name`, `house_desc`, `house_buy_price`, `house_sale_price`) VALUES (NULL, 'Cardboard Box', 'Wow, it\'s cardboard', 0, 0);

INSERT INTO `mydb`.`houses` (`house_id`, `house_name`, `house_desc`, `house_buy_price`, `house_sale_price`) VALUES (NULL, 'Basher', 'A MoD issued \'tent\'', 100, 25);

COMMIT;[/mysql]

[a_bertrand]

Questions:

- why using innodb? Are you sure it's the right DB engine for you?

- why having foreign key "without actions". Seems a bit odd. Normally either you want to prevent the deletion if there is still some child, or you delete all the childs if you delete the mater record.

[Karlos94]

I read somewhere that only InnoDB currently supports foreign keys? Am I wrong to think MyISAM dosen't support foreign keys?

Well some of the foreign keys don't have actions as I am still getting the concept of them, I am getting to understand them better.. CASCASE, RESTRICT, SET NULL and NO ACTION.

[a_bertrand]

indeed myisam doesn't have foreign key, but are you REALLY sure you need them? Foreign keys means slower updates / inserts as the DB must check the validity of the data. On my own game I never used them (as I'm mainly working with myisam and some memory table), and never really missed the functionality they could provide. The worse case scenario is that you delete the master record and you leave some parent less child. Not such a big deal.

[Lithium]

As a_bertrand pointed correctly, foreign keys are way slower, and also as he stated, myisam and memory tables are one way to go, though you can still (should) use InnoDB for "static" (the ones that usually have very little updates/inserts/deletes) tables. That if you are performance concerned! So a mix on the 3 would be the perfect choice, MyIsam, InnoDB and Memory!

[Karlos94]

Ahh I see, could it I be an ass and ask what type of tables engine would be most efficient with certian types of data stored in there?

 

• Updating alot?
  
• Inserting and selecting?
  
• Selecting and updating?
  
• ect..

[a_bertrand]

InnoDB should be good if you have nearly static data, that means, maybe a lot of data but doesn't change much.

MyISAM is an average engine for most operations, which means, it's not bad, simply it will not be the best performer of any. Good choice if you have a mixed usage, like either a lot of inserts or quiet a lot of updates / deletes.

Memory is an excellent engine if the data is small and doesn't need to be kept in case your restart the DB.

Now of course you may have times where you need absolutely one function of a given engine, and therefore accept to pay the trade off.

[Karlos94]

For example:

 

• InnoDb - Records and logs?
  
• MyISAM - General users table and tables with updates and inserts?
  
• Memory - To be honest, I'm not quite sure.

[fortuente]

Hi, my first post!

InnoDB and MyISAM have pros and cons and I don't think one is necessarily better or worse for a web game. In the game I am currently working on I am using InnoDB exclusively because I am want to focus on data integrity. Or that is what I tell myself, lol. My only real qualm about using InnoDB so much right now is that it is more resource-intensive.

Also, I believe the db roles in this thread should be reversed: InnoDB is actually better on tables with frequent inserts and updates because it uses row-level locking (as opposed to table-level locking) and MyISAM is better on tables with a lot of static data as it has full-text indexing, so it reads and searches "better.

[Henrietta]

Row-level locking in InnoDB may really prove useful and actually can provide a speed increase, as you don't need to lock whole tables during sensitive operations.

Unless you don't care about locking at all :D

[a_bertrand]

True, table locks are evil, however they are very quick and less resource intensive. Also, before jumping on InnoDB really evaluate the pro and cons, as InnoDB is not the best solution in all cases and will actually less features in some departments like backups. What you could do is to try to convert some tables from MyISAM to InnoDB, the tables which seems to produce the higher number of locks and see if you see an increase in speed.