如何根据事件关联 3 个表 [英] How to relate 3 tables depending on event

问题描述

我有一张表格，其中包含关于不同类型的事件的信息，这些事件可以由两类人员完成公民和工人

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)

这篇关于如何根据事件关联 3 个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！