A Truly Unique Field

Databases
1

Let’s say I’m designing an employee database for a secret spy agency. This agency has all different kinds of employees (spies, janitors, middle managers, etc), all of which need to be in the db. Each kind of employee has its own fields, so each kind of employee needs to have its own table.

For example: On the “spies” table, the fields might be “primary weapon,” “current location,” “kill count,” etc. But for middle managers, the fields could be “department” and “budget.”

But ALL employees need to have a “panic phrase” that is unique to them (in case they’re captured and can only get a single word out to the agency). I can’t have a unique “panic phrase” field in each table, because that could allow for duplicate panic phrases in different tables.

I could put all the panic phrases in their own table and just have a “panic_id” field in the employee tables, but then we have the same problem: two employees on different tables could point to the same panic phrase id.

How do I design the database to make this work? Or is there no solution, other than checking each field in each table?

2

it’s really simple, you will need a “supertype” table and several “subtype” tables

you’ve used the term “employee” consistently, so let’s call the supertype table the employees table

their employee id would go in here as the PK, as well as any columns that they all share – and panic phrase fits the bill perfectly

then in each of the subtype tables, managers, spies, etc., they would have their individual custom columns, as well as their employee id as PK, but also simultaneously as FK to the employees table

make sense?

3

A ha! Of course. Thank you.