mysql - 数据库设计问题和帐户删除后删除的邮件

Question

我的网站由两种类型的用户组成，普通用户和企业用户以及 super 管理员。所有这些都列在用户表(名称、电子邮件、密码)中，并添加了business_users 表，该表保存业务用户的仅与业务相关的信息(业务类型、地址等)。

还有第三个表保存该站点的邮件。从普通用户发送到业务用户或 super 管理员(支持等...)的邮件。

因此，这个邮件表有 2 个列(从，到)，它们引用用户表的 userID 列。问题是，假设business_user删除了他的帐户。我打算也删除他发送的邮件(无论是发给普通用户还是 super 管理员)问题是mails表的结构方式不可避免地要删除上面描述的邮件，我也会删除super_admin的普通用户发送给HIM的邮件。

检查了 2 个场景:

1. 根本不删除任何邮件 - 在这种情况下，讨论到此结束。
2. 重新设计数据库，以便发送给他的邮件不会被删除(reg_users 的屏幕中仍会保留邮件)，但另一方面，他发送的邮件会被删除。

你有什么建议？如果选择第二个选项，我应该如何设计数据库。这是 3 个表:

CREATE  TABLE IF NOT EXISTS `alternative_appointm`.`users` (
`user_ID` INT(11) NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(45) NOT NULL ,
`lastname` VARCHAR(45) NOT NULL ,
`email` VARCHAR(255) NULL DEFAULT NULL ,
`password` VARCHAR(100) NULL DEFAULT NULL ,
`hash` VARCHAR(32) NULL DEFAULT NULL ,
`usertype` ENUM('1','2','3','4') NULL DEFAULT NULL ,
 PRIMARY KEY (`user_ID`) ,
 INDEX `fk_users_usertype1_idx` (`usertype` ASC) ,
 CONSTRAINT `fk_users_usertype1`
 FOREIGN KEY (`usertype` )
 REFERENCES `alternative_appointm`.`usertype` (`type_id` )
 ON DELETE NO ACTION
 ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8

CREATE  TABLE IF NOT EXISTS `alternative_appointm`.`business_users` (
`crID` INT(11) NOT NULL ,
`address` VARCHAR(45) NULL DEFAULT NULL ,
`url` VARCHAR(45) NULL DEFAULT NULL ,
`phone` VARCHAR(20) NULL ,
`city` VARCHAR(100) NULL ,
`municipality` VARCHAR(100) NULL ,
`bus_user_type` ENUM('1','2','3','4','5') CHARACTER SET 'latin1' NULL ,
 PRIMARY KEY (`crID`) ,
 INDEX `fk_business_users_buz_usertype1_idx` (`bus_user_type` ASC) ,
 INDEX `crID` (`crID` ASC) ,
 CONSTRAINT `fk_business_users_buz_usertype1`
 FOREIGN KEY (`bus_user_type` )
 REFERENCES `alternative_appointm`.`buz_usertype` (`Type_id` )
 ON DELETE CASCADE
 ON UPDATE CASCADE,
 CONSTRAINT `fk_business_users_users1`
 FOREIGN KEY (`crID` )
 REFERENCES `alternative_appointm`.`users` (`user_ID` )
 ON DELETE CASCADE
 ON UPDATE CASCADE)
 ENGINE = InnoDB
 DEFAULT CHARACTER SET = utf8


CREATE  TABLE IF NOT EXISTS `alternative_appointm`.`mails` (
`message-iD` INT(10) NOT NULL AUTO_INCREMENT ,
`from` INT(11) NULL DEFAULT NULL ,
`to` INT(11) NULL DEFAULT NULL ,
`datetime` DATETIME NULL DEFAULT NULL ,
`subject` TEXT NULL DEFAULT NULL ,
`message` TEXT NULL DEFAULT NULL ,
 PRIMARY KEY (`message-iD`) ,
 UNIQUE INDEX `message-iD_UNIQUE` (`message-iD` ASC) ,
 INDEX `fk_mails_users1_idx` (`from` ASC) ,
 INDEX `fk_mails_users2_idx` (`to` ASC) ,
 CONSTRAINT `fk_mails_users1`
 FOREIGN KEY (`from` )
 REFERENCES `alternative_appointm`.`users` (`user_ID` )
 ON DELETE NO ACTION
 ON UPDATE NO ACTION,
 CONSTRAINT `fk_mails_users2`
 FOREIGN KEY (`to` )
 REFERENCES `alternative_appointm`.`users` (`user_ID` )
 ON DELETE NO ACTION
 ON UPDATE NO ACTION)
 ENGINE = InnoDB
 DEFAULT CHARACTER SET = utf8

Answer 1

在表alternative_appointm.mails上，您必须进行完整性约束。目前:

CONSTRAINT `fk_mails_users1`
FOREIGN KEY (`from` )
 REFERENCES `alternative_appointm`.`users` (`user_ID` )
 ON DELETE NO ACTION
 ON UPDATE NO ACTION,

和

 CONSTRAINT `fk_mails_users2`
 FOREIGN KEY (`to` )
 REFERENCES `alternative_appointm`.`users` (`user_ID` )
 ON DELETE NO ACTION
 ON UPDATE NO ACTION)

第一步是添加“ON UPDATE CASCADE”，您应该在大多数约束下执行此操作，这意味着如果您更新用​​户 ID，它将直接在 from 上更新 或到 列。

接下来的事情是，当在 users 表上删除用户时，您希望删除他发送的所有邮件，而不是他收到的邮件。因此，您只想删除可以在 from 列中找到用户 ID 的邮件。

因此，更新第一个约束，仅更新此约束，而不是有关 'to' 列的约束:

CONSTRAINT `fk_mails_users1`
FOREIGN KEY (`from` )
 REFERENCES `alternative_appointm`.`users` (`user_ID` )
 ON DELETE CASCADE
 ON UPDATE CASCADE,

如果某些数据引用了您要删除的用户 ID，第二个约束将阻止删除该用户。要允许删除，您应该清空该字段(NO ACTION 与 RESTRICT 相同)通过使用此约束:

 CONSTRAINT `fk_mails_users2`
 FOREIGN KEY (`to` )
 REFERENCES `alternative_appointm`.`users` (`user_ID` )
 ON DELETE SET NULL
 ON UPDATE CASCADE)

您将有一个有效的删除，发送给该用户的邮件仍然在表中，但是，您在“收件人”列中没有该用户的 ID(并且该 ID 不再存在)。您可以使用事务来更改受影响的行，并在“收件人”列中设置一个特殊的 ID，类似于默认的“已删除用户”用户帐户 ID。在 postgreSQl 上，您将具有“ON DELETE SET DEFAULT”，但这在 MySQL AFAIK 中不存在。