如何根据事件关联3个表

本文介绍了如何根据事件关联3个表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一张表格，其中包含有关不同类型事件的信息，可以由两个类别民事和工作人员

I have a table that have information about different types of events that can be done by persons in two categories civil and worker

对于他们中的每一个我有各自的表

so for each one of them I have their respective tables

civil{ civil_id, name, age,telephone...} the primary key is civil_id worker{ worker_id, name, duty, department...} the primary key is worker_id

然后事件表中列出了所有可能的事件

then the event table has a list of all possible events

event {type_of_event} the primary key is type_of_event

然后我打算将信息存储在其他表中

then I am planing to store information in other table

with eventype，做这个工作的人（工人或民事）

with eventype, the person that did the job (worker or civil)

id event_type date person ----------------------------------- 1 type1 12-12-12 x 2 type1 05-12-10 y 3 type2 02-12-12 y

现在在这个设计中，我不知道如何联系谁的工作，如果我只有一种人（也就是民事），我只会在这个最后一张表中存储civil_id在人物领域....但是如何要知道是民事还是工作人员，是否需要其他中间表？

Now in this design I do not know how to relate whose person did the job if, I would had only a kind of person (aka civil) i would only store the civil_id in person field in this last table....but how to know if it was civil or worker, do I need other intermediate table?

推荐答案

在这种情况下，您无法设置外键，因为您有多个父级。为了快速搜索或避免全表扫描在表上的列上定义一个索引 person c>事件并使用 LEFT JOIN 加入表。例如，

In this can, you cannot set up a foreign key because you have multiple parent. In order to do fast search or to avoid full table scan define an index on column person on table event and join the table using LEFT JOIN. eg,

SELECT ...., COALESCE(b.name, c.name) AS personname FROM event a LEFT JOIN civil b ON a.person = b.civil_id LEFT JOIN worker c ON a.person = c.worker_ID

添加 INDEX

ALTER TABLE event ADD INDEX (person)