CODEWITHSUNDEEP
sqldatabasedatabase-design
J.Kirk.
J.Kirk.

Reputation: 973

Recursive association with inheritance

If my inherited table has recursive association does the foreign key point to the primary key of this table or the parent table?

In my scenario:

Is this table correct or should the second constraint reference Person(person_id) instead?

create table Employee(
  person_id int not null,
  salary float not null,
  etype varChar(25) not null,
  manager_id int not null,
  dept_id int not null,

  primary key(person_id),
  constraint fkemp foreign key(person_id) references Person(person_id),
  constraint fkmanager foreign key(manager_id) references Employee(person_id),
  constraint fkdept foreign key(dept_id) references Department(dept_id),
);

Also the CEO has nobody managing him does that mean I need to compromise with my data integrity and allow a null value?

Upvotes: 0

Views: 253

Answers (1)

Jerrad
Jerrad

Reputation: 5290

I think the table is correct. For the purposes of the hierarchy, managers are Employees (their People properties aren't really relevant here).

Secondly, you can make manager_id nullable and not compromise your data integrity. Clearly, there are situations where an employee does not have a manager (i.e. the CEO), so it isn't really any sort of logical violation to allow manager_id to be null.

If it makes you feel safer, you could add a is_manager bit not null column to the Employee table, and then have a constraint that only allows a null manager_id if is_manager is true.

Upvotes: 1

Related Questions