有两个对象,个人person
、活动event
。每个person
可以参加多个event
,每个event
可以由多person
参加。数据库表格如下:
event
- id [int (4),primary]
- name [varchar (30)]
- …
person
- id [int (4),primary]
- name [varchar (30)]
- mail [varchar (60),unique]
- attends [Array of events.id]
如果要将这两张表进行关联,需要在person
和event
之间构造关系表。
CREATE TABLE attendance (
id INT AUTO_INCREMENT,
person_id INT NOT NULL,
event_id INT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY fk_person (person_id) REFERENCES person (id),
FOREIGN KEY fk_event (event_id) REFERENCES event (id)
);
ALTER TABLE attendance
ADD CONSTRAINT attendance_pk UNIQUE (person_id, event_id);
两个外键分别关联到person
、event
。
如果需要查询参加具体活动event
所有人person
的姓名,sql语句如下:
SELECT p.name
FROM
event AS e
INNER JOIN attendance AS a ON a.event_id = e.id
INNER JOIN person AS p ON p.id = a.person_id
WHERE e.name = 'The Foo Event';