Referential Integrity - Indirect Foreign Key In "Depth"

Question

What is the best practice for ensuring a Key from a "Grand-Parent" or "Great Grand-Parent" table is maintained when a "Child" or "Grand-Child" table is created from multiple relationship trees.

Details Since That Question Doesn't Likely Make Sense

I am attempting to build a database for keeping track of the execution status of automated processes running in our environment.

In general we have a "Job" which triggers one or more "Executables" and those "Executables" can run tasks for one or more customers. We will then have 2 logging tables, one that tracks when a "Job" was started, and another that will log the Success vs Failure status of each "ExecutableCustomer" instance.

A Planned Simplified Schema is below:

When we right the record to the JobExecutableCustomerExecutionLog, I would like to ensure that the Job.ID value associated with JobExecutionLog.ID value matches the Job.ID value associated with JobExecutableCustomer.ID.

Normally I would handle this with a Foreign Key but since Job.ID is not stored on JobExecutableCustomer, JobExecutableCustomerExecutionLog nor JobExecutionLog. The Relationship is indirect.

Example:

I have 2 jobs, "Send Email" and "Send Text Message". "Send Email" initiates a single executable which belongs to 1 Customer. "Send Text Message" has 2 executables (both of which execute for the same customer). I want to make sure that when the record is written to JobExecutableCustomerExecutionLog for "Send Email" the Job.ID associated with JobExecutableCustomerExecutionLog.JobExecutableCustomerID and JobExecutableCustomerExecutionLog.JobExecutionLogID (after walking the relationships up) actually belong to the Job.ID for "Send Email" not "Send Text Message".

As I see it I have 2 options:

• Push the value from Job.ID into all the child tables, and make it part of the Foreign Key

• Have another process (Trigger or Indexed View) ensure the relationships for me

I personally don't like the

I personally don't like the idea of pushing the Job.ID value on all the other child tables

Why not? The obvious solution is to make JobID the leading PK/clustered index column on all the child tables. And it ensures optimal performance for accessing all these tables by JobID.

Generally whenever you have an "identifying relationship" aka "child table" aka "weak entity" the child table should use a compound primary key whose leading columns are also the foreign key to the parent table. Something like this:

create table Parent
(
  ParentId int not null identity,
  constraint pk_Parent
    primary key(ParentId)
)

create table Child
(
  ParentId int not null, 
  ChildId int not null identity, 
  constraint pk_Child 
    primary key (ParentId,ChildId),
  constraint fk_Child_Parent
    foreign key (ParentId) references Parent(ParentId)
    on delete cascade
)
create table GrandChild
(
  ParentId int not null references Parent, 
  ChildId int not null, 
  GrandChildId int not null identity,
  constraint pk_GrandChild 
    primary key (ParentId,ChildId,GrandChildId),
  constraint fk_GrandChild_Child
    foreign key (ParentId) references Parent(ParentId)
    on delete cascade
)

StackExchange Database Administrators Q#267173, answer score: 3