用于配对网络活动参与者的关系数据库模式和查询

Question

我正在为一个网络活动的配对与会者的应用程序工作，并没有太多的经验与关系数据库。每一轮(10分钟)，参与者将被配对以认识彼此。该应用程序不应该在随后的回合中将与会者与同一个人匹配。

这是我最初设计的模式:

CREATE TABLE IF NOT EXISTS attendees (
id SERIAL,
name varchar NOT NULL,
CONSTRAINT pk_attendees_id PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS matches (
id SERIAL,
attendee_id integer NOT NULL,
partner_id integer NOT NULL,
has_met boolean DEFAULT false,
CONSTRAINT pk_matches_id PRIMARY KEY (id),
CONSTRAINT fk_attendee_id FOREIGN KEY (attendee_id) REFERENCES attendees(id),
CONSTRAINT fk_partner_id FOREIGN KEY (partner_id) REFERENCES attendees(id)
);

样本数据:

INSERT INTO attendees (name) VALUES
('john'),
('leland'),
('becky'),
('jerome'),
('sarah'),
('ahmed'),
('mike'),
('greg'),
('jessica'),
('latoya'),
('emily'),
('jake');
INSERT INTO matches (attendee_id, partner_id, has_met) VALUES
(1, 4, true),
(4, 1, true),
(3, 2, true),
(2, 3, true),
(5, 6, true),
(6, 5, true),
(7, 9, true),
(9, 7, true),
(5, 1, true),
(1, 5, true),
(7, 3, true),
(3, 7, true),
(2, 6, true),
(6, 2, true),
(8, 3, true),
(3, 8, true);

下面的查询返回所有可以进行的唯一匹配，而不允许任何已经匹配的参与者在本轮中再次配对:

SELECT a.id, a.name, b.id, b.name
FROM attendees a
CROSS JOIN attendees b
WHERE a.id < b.id
AND b.id NOT IN (
SELECT m.partner_id
FROM matches m
WHERE a.id = m.attendee_id
)
ORDER BY a.id;

在我的应用程序中，我能够获取上述查询的结果并为每个与会者选择配对。是否有可能修改查询，使其只返回每个与会者的单个结果?

上述方法可能是错误的，因为数据没有标准化。我得出这个结论对吗?

我已经尝试重新设计架构以不重复数据:

CREATE TABLE IF NOT EXISTS attendees (
id SERIAL,
name varchar NOT NULL,
CONSTRAINT pk_attendees_id PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS matches (
id SERIAL,
has_met boolean DEFAULT false,
CONSTRAINT pk_matches_id PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS participants (
id SERIAL,
match_id integer NOT NULL,
attendee_id integer NOT NULL,
CONSTRAINT pk_participants_id PRIMARY KEY (id),
CONSTRAINT fk_matches_match_id FOREIGN KEY (match_id) REFERENCES matches(id),
CONSTRAINT fk_matches_attendee_id FOREIGN KEY (attendee_id) REFERENCES attendees(id)
);

样本数据:

INSERT INTO attendees (name) VALUES
('john'),
('leland'),
('becky'),
('jerome'),
('sarah'),
('ahmed'),
('mike'),
('greg'),
('jessica'),
('latoya'),
('emily'),
('jake');
INSERT INTO matches (has_met) VALUES
(true),
(true),
(true),
(true),
(true),
(true);
INSERT INTO participants (match_id, attendee_id) VALUES
(1, 1),
(1, 3),
(2, 4),
(2, 12),
(3, 5),
(3, 7),
(4, 9),
(4, 8),
(5, 11),
(5, 6),
(6, 2),
(6, 10);

上述数据库模式设计正确吗?

如何执行查询以返回每轮的可能配对，以便没有重复?是否有可能让数据库确定每个回合的确切配对，或者这是必须在应用程序中完成的事情?

Answer 1

根据你的描述，你有两个实体:

• attendees
• matches

"attendees"可能是参与者;我不确定。

然后有一个连接表将它们组合在一起:

• matchAttendees

每个匹配和每个参与者将有一行。

如果你想知道谁在之前的匹配中没有被匹配，你可以在这些表上执行一个查询。