How to relate 3 tables depending on event

Question

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, 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

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?

Answer 1

Generally, there are to ways to model this type of situation...

Using Exclusive Foreign Keys

In the event, both civil_id and worker_id are NULL-able, but there is also a constraint ensuring exactly one of them is non-NULL at any given time:

CHECK (
    (civil_id IS NOT NULL AND worker_id IS NULL)
    OR (civil_id IS NULL AND worker_id IS NOT NULL)
)

Using Inheritance¹

For more on inheritance, take a look at "Subtype Relationships" chapter in the ERwin Methods Guide and at this post.

---

¹ Aka. category, subtyping, subclassing, generalization hierarchy...