How to model inheritance of two tables MySQL

I have some tables where I store data and depending on the type of person (worker / civil) that did a job I want to store it in an event table, now these guys rescue an animal (there is an animal table).

Finally, I want to have a table to store the event that a guy (worker / civil), saved an animal, but how should I add a foreign key or how to know the id value of the civil or worker that did the job?

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

How to reflect the design of the following diagram in MySQL?

Additional details

I have modelled it the following way:

``
DROP TABLE IF EXISTS tbl_animal;
CREATE TABLE tbl_animal (
id_animal INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(25) NOT NULL DEFAULT "no name",
specie VARCHAR(10) NOT NULL DEFAULT "Other",
sex CHAR(1) NOT NULL DEFAULT "M",
size VARCHAR(10) NOT NULL DEFAULT "Mini",
edad VARCHAR(10) NOT NULL DEFAULT "Lact",
pelo VARCHAR(5 ) NOT NULL DEFAULT "short",
color VARCHAR(25) NOT NULL DEFAULT "not defined",
ra VARCHAR(25) NOT NULL DEFAULT "not defined",
CONSTRAINT uc_Info_Animal UNIQUE (id_animal)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO tbl_animal VALUES (1,'no name', 'dog', 'M','Mini','Lact','Long','black','Bobtail');
INSERT INTO tbl_animal VALUES (2,'peluchin', 'cat', 'M','Mini','Lact','Long','white','not defined');
INSERT INTO tbl_animal VALUES (3,'asechin', 'cat', 'M','Mini','Lact','Corto','orange','not defined');

DROP TABLE IF EXISTS tbl_person;
CREATE TABLE tbl_person` (
type_person VARCHAR(50) NOT NULL primary key
) ENGINE=InnoDB DEFAULT CHARSE

Since I made the diagram, I better answer ;)

Current relational databases unfortunately don't support the inheritance directly, therefore you need to transform it into "plain" tables. There are generally 3 strategies for doing so:

• All classes1 in a single table with NULL-able non-common fields.

• Concrete classes2 in separate tables. Abstract classes don't have the tables of their own.

• All classes in separate tables.

For more on what this actually means and some pros and cons, please see the links provided in my original post, but in a nutshell the (3) should probably be your default unless you have a specific reason for one of the other two. You can represent the (3) in the database simply like this:

CREATE TABLE person (
    person_id int PRIMARY KEY
    -- Other fields...
);

CREATE TABLE civil (
    civil_id int PRIMARY KEY REFERENCES person (person_id)
    -- Other fields...
);

CREATE TABLE worker (
    worker_id int PRIMARY KEY REFERENCES person (person_id)
    -- Other fields...
);

CREATE TABLE event (
    event_id int PRIMARY KEY,
    person_id int REFERENCES person (person_id)
    -- Other fields...
);

Unfortunately, this structure will let you have a person that is neither civil nor worker (i.e. you can instantiate the abstract class), and will also let you create a person that is both civil and worker. There are ways to enforce the former at the database level, and in a DBMS that supports deferred constraints3 even the latter can be enforced in-database, but this is one of the few cases where using the application-level integrity might actually be preferable.

1 person, civil and worker in this case.

2 civil and worker in this case (person is "abstract").

3 Which MySQL doesn't.

StackExchange Database Administrators Q#36573, answer score: 22