MySQL-三个实体之间的多对多关系

Question

我很难为数据库中的多对多关系找到最佳设计。我的项目允许用户创建我们称之为日志警报的东西。日志警报将检查给定日志是否符合某些标准，如果符合，则会向AWS SNS主题发送消息。我想做的是将日志警报与AWS SNS主题关联起来。我还想把哪个用户分配的日志警报与AWS SNS主题联系起来。

我有一个表类XRefUserLogAlarmSNSTopic。它有三把外键。该表的目标是将哪些SNS主题与哪些日志警报相关，并指示哪个用户建立了关系。这对我来说似乎相当混乱，当我试图在SpringJPA中创建新的日志警报或联接表时，我会遇到各种错误。我的问题是，对于我试图实现的，是否有更好的数据库结构

UserId INT NOT NULL AUTO_INCREMENT,
Username VARCHAR(50) NOT NULL,
Password TEXT NOT NULL,
Email VARCHAR(255) NOT NULL,
Dashboard LONGTEXT NOT NULL,
PRIMARY KEY (UserId),
UNIQUE (Username),
UNIQUE (Email)
);
CREATE TABLE SNSTopics (
SNSTopicId INT NOT NULL AUTO_INCREMENT,
TopicName VARCHAR(50) NOT NULL,
TopicArn VARCHAR(255) NOT NULL,
PRIMARY KEY (SNSTopicId),
UNIQUE (TopicName),
UNIQUE (TopicArn)
);
CREATE TABLE LogGroups (
LogGroupId INT NOT NULL AUTO_INCREMENT,
Name VARCHAR(255) NOT NULL,
PRIMARY KEY (LogGroupId),
UNIQUE (Name)
);
CREATE TABLE Keywords (
KeywordId INT NOT NULL AUTO_INCREMENT,
Word VARCHAR(70),
PRIMARY KEY (KeywordId),
UNIQUE (Word)
);
CREATE TABLE LogAlarms (
LogAlarmId INT NOT NULL AUTO_INCREMENT,
LogLevel VARCHAR(5) NOT NULL CHECK (LogLevel IN ('TRACE', 'DEBUG', 'INFO', 'WARN', 'ERROR')),
Comparison VARCHAR(2) CHECK (Comparison IN ('==', '<', '<=', '>', '>=')),
AlarmName VARCHAR(255) NOT NULL,
KeywordRelationship CHAR(3) CHECK (KeywordRelationship IN ('ANY', 'ALL', NULL)),
PRIMARY KEY (LogAlarmId),
UNIQUE (AlarmName)
);
CREATE TABLE MetricAlarms (
MetricAlarmId INT NOT NULL AUTO_INCREMENT,
AlarmArn VARCHAR(100) NOT NULL,
PRIMARY KEY (MetricAlarmId),
UNIQUE (AlarmArn)
);
CREATE TABLE XRefUserMetricAlarm (
UserMetricAlarmId INT NOT NULL AUTO_INCREMENT,
UserId INT NOT NULL,
MetricAlarmId INT NOT NULL,
PRIMARY KEY (UserMetricAlarmId),
FOREIGN KEY (UserId) REFERENCES Users(UserId) ON DELETE CASCADE,
FOREIGN KEY (MetricAlarmId) REFERENCES MetricAlarms(MetricAlarmId) ON DELETE CASCADE,
UNIQUE (UserId, MetricAlarmId)
);
CREATE TABLE XRefLogAlarmLogGroup (
LogAlarmLogGroupId INT NOT NULL AUTO_INCREMENT,
LogAlarmId INT NOT NULL,
LogGroupId INT NOT NULL,
PRIMARY KEY (LogAlarmLogGroupId),
FOREIGN KEY (LogAlarmId) REFERENCES LogAlarms(LogAlarmId) ON DELETE CASCADE,
FOREIGN KEY (LogGroupId) REFERENCES LogGroups(LogGroupId) ON DELETE CASCADE,
UNIQUE (LogAlarmId, LogGroupId)
);
CREATE TABLE XRefLogAlarmKeyword (
LogAlarmKeywordId INT NOT NULL AUTO_INCREMENT,
LogAlarmId INT NOT NULL,
KeywordId INT NOT NULL, 
PRIMARY KEY (LogAlarmKeywordId),
FOREIGN KEY (LogAlarmId) REFERENCES LogAlarms(LogAlarmId) ON DELETE CASCADE,
FOREIGN KEY (KeywordId) REFERENCES Keywords(KeywordId) ON DELETE CASCADE,
UNIQUE (LogAlarmId, KeywordId)
);
CREATE TABLE XRefUserLogAlarmSNSTopic (
UserLogAlarmSNSTopicId INT NOT NULL AUTO_INCREMENT,
LogAlarmId INT NOT NULL,
SNSTopicId INT NOT NULL,
UserId INT NOT NULL,
PRIMARY KEY (UserLogAlarmSNSTopicId),
FOREIGN KEY (LogAlarmId) REFERENCES LogAlarms(LogAlarmId) ON DELETE CASCADE,
FOREIGN KEY (SNSTopicId) REFERENCES SNSTopics(SNSTopicId) ON DELETE CASCADE,
FOREIGN KEY (UserId) REFERENCES Users(UserId) ON DELETE CASCADE,
UNIQUE (LogAlarmId, SNSTopicId, UserId)
);```

Answer 1

为了匹配您的描述，您的XRefUserLogAlarmSNSTopic不正确。

实际上，您不想链接三个实体，只想链接两个：您想将日志警报与AWS SNS主题关联起来(这是标识该关系的两个值(，然后将用户作为属性添加到该关系中。尽管在这种情况下，这个特定属性指的是另一个实体，但它在逻辑上与存储(该用户(创建关系的时间戳没有本质区别。

与当前表的区别在于主键/唯一键：如果不同的用户添加了报警和主题之间的关系，则当前表允许您多次添加它们，因为只有(alarm, topic, user)需要唯一，而不是(alarm, topic)是唯一的，user是该关系的属性。

如果您想要UserId列的ON DELETE CASCADE，我也会考虑。虽然LogAlarmId和SNSTopicId的级联是有意义的，但在删除创建它们的用户时，并不明显需要删除所有关系(当然这取决于您的需求(。最好将它们设置为null。所以我建议用这样的桌子

CREATE TABLE XRefLogAlarmSNSTopic (   -- user not part of table name
LogAlarmSNSTopicId INT NOT NULL AUTO_INCREMENT,  
LogAlarmId INT NOT NULL,
SNSTopicId INT NOT NULL,
UserId INT NULL,  -- null
PRIMARY KEY (UserLogAlarmSNSTopicId),
FOREIGN KEY (LogAlarmId) REFERENCES LogAlarms(LogAlarmId) ON DELETE CASCADE,
FOREIGN KEY (SNSTopicId) REFERENCES SNSTopics(SNSTopicId) ON DELETE CASCADE,
FOREIGN KEY (UserId) REFERENCES Users(UserId) ON DELETE SET NULL,  -- set null
UNIQUE (LogAlarmId, SNSTopicId)  -- user not part of primary key candidate
)

显然，您也有可能想要这种三向关系，例如，每个用户都创建了自己的警报主题关系，在这种情况下，您的表是正确的，只是您的描述不够精确。