如何根据事件关联 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

使用 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

现在在这个设计中，我不知道如何关联谁做了这项工作，如果我只有一种人(又名民事)，我只会在最后一张表中的 person 字段中存储 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?

推荐答案

在这个can中，你不能设置外键，因为你有多个父级.为了进行快速搜索或避免 full table scan 在表 event 的列 person 上定义索引并使用 加入表左连接.例如，

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)