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

Question

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

    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
)

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