Foreign key referring to primary keys across multiple tables?

Question

I have two tables namely employees_ce and employees_sn under the database employees.

They both have their respective unique primary key columns.

I have another table called deductions, whose foreign key column I want to refer to primary keys of employees_ce as well as employees_sn. Is this possible?

for example

employees_ce

--------------

empid   name

khce1   prince

employees_sn

----------------

empid   name

khsn1   princess

so is this possible?

deductions

--------------

id      name

khce1   gold

khsn1   silver

Answer 1

Try doing as follows:

Start from a higher-level description of the database! As you have employees, and employees can be "ce" employees and "sn" employees (whatever those are). In oo(object-oriented terms), there is a class named as "employee", with two sub-classes named as "ce employee" and "sn employee". Now, you can translate this higher-level description to three tables as follows: employees, employees_ce and employees_sn:

employees(id, name)

employees_ce(id, ce-specific stuff)

employees_sn(id, sn-specific stuff)

Since all employees are employees, every employee must have a row in the employees table. "ce" employees will also have a row in the employees_ce table, and "sn" employees will also have a row in the employees_sn table. Here, employees_ce.id is the foreign key to employees.id, just as employees_sn.id is.

Refer to the employees table: you can refer to an employee of any kind like (ce or sn).