Reputation: 3
I have some problems to find a decission for an implementation.
I have to design a new database using mysql. Now I found that I can do mysqlinternal actions on update or delete (NULL/NO ACTION / CASCADE).
EXAMPLE USING ON DELETE MYSQL INTERNAL:
CREATE TABLE IF NOT EXISTS `mydb`.`users` (
`idusers` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`name` CHAR(30) NOT NULL ,
`mail` CHAR(50) NOT NULL ,
PRIMARY KEY (`idusers`) )
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `mydb`.`avatars` (
`users_idusers` INT UNSIGNED NOT NULL ,
`size` VARCHAR(45) NOT NULL ,
`color` VARCHAR(45) NOT NULL ,
`weight` VARCHAR(45) NOT NULL ,
PRIMARY KEY (`users_idusers`) ,
CONSTRAINT `fk_avatars_users`
FOREIGN KEY (`users_idusers` )
REFERENCES `mydb`.`users` (`idusers` )
ON DELETE CASCADE
ON UPDATE NO ACTION)
ENGINE = InnoDB;
Or is this way better and do each update delete completly from Application code:
CREATE TABLE IF NOT EXISTS `mydb`.`users` (
`idusers` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`name` CHAR(30) NOT NULL ,
`mail` CHAR(50) NOT NULL ,
PRIMARY KEY (`idusers`) )
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `mydb`.`avatars` (
`users_idusers` INT UNSIGNED NOT NULL ,
`size` VARCHAR(45) NOT NULL ,
`color` VARCHAR(45) NOT NULL ,
`weight` VARCHAR(45) NOT NULL ,
PRIMARY KEY (`users_idusers`) ,
CONSTRAINT `fk_avatars_users`
FOREIGN KEY (`users_idusers` )
REFERENCES `mydb`.`users` (`idusers` ))
ENGINE = InnoDB;
Now, I'm not sure which is the best way which ensures a high performace level.
Should I implement relations in my programminglanguage and do a query on tables wich are relatet to my current dataset or should I implement the database in a way, which allows a mysql internal upate/delete Action
Thanks in advance.
Upvotes: 0
Views: 123
Reputation: 5136
My suggestion would be to implement the constraints in your RDBMS. It provides data integrity, better performance, less coding, and less chance of inconsistencies caused by the programmer.
Upvotes: 1
Reputation: 109
i would like to leave all of this relations to the Database ...relations in database is better than implementing it in the code ... reliable , optimized , performance better .
Upvotes: 1