database design - How can I enforce second-degree relationships without composite keys? -

consider database design multi-tenancy line-of-business web application:

a tenant tenant of web-application, tenant has many shops , many customers (customer records not shared between tenants, it's valid multiple customer records refer same real-life human), , each shop has many jobs. job associated each customer.

there exists problem in there doesn't seem trivial constraint solution prevent case job's customerid changed customer not belong parent tenant, creating invalid data.

here present schema:

create table tenants (     tenantid bigint identity(1,1) primary key     ... )  create table shops (     tenantid bigint foreign key( tenants.tenantid ),     shopid   bigint identity(1,1) primarey key,     ... )  create table customers (     tenantid   bigint foreign key( tenants.tenantid ),     customerid bigint identity(1,1) primary key     ... )  create table jobs (     shopid bigint foreign key( shops.shopid )     jobid bigint identity(1,1) primary key,     customerid bigint foreign key( customers.customerid ) ) 

currently solution can think of change design use composite keys include parent tenant.tenantid, shared accordingly:

create table shops (     tenantid bigint,     shopid   bigint identity(1,1),     ...      primary key( tenantid, shopid )     foreign key( tenantid references tenants (tenantid) ) )  create table customers (     tenantid   bigint,     customerid bigint identity(1,1)     ...      primary key( tenantid, customerid )     foreign key( tenantid references tenants (tenantid) ) )  create table jobs (     tenantid bigint     shopid bigint     jobid bigint identity(1,1),     customerid bigint      primary key( tenantid, shopid, jobid )      foreign key( tenantid references tenants ( tenantid ) )     foreign key( tenantid, shopid references shops( tenantid, shopid ) )     foreign key( tenantid, customerid references customers( tenantid, customerid ) ) ) 

...seems bit of hack though, lots of redundant data - identity used anyway. there way rdbms can test joins consistency whenever data mutated?

composite foreign key constraints valid , useful, don't need composite primary keys use them! need composite indices in referenced tables. redundant tenantid in jobs won't create risk of update anomalies fk constraints.

for example:

create table shops (     shopid   bigint identity(1,1),     tenantid bigint,     primary key (shopid),     unique key (tenantid, shopid),     foreign key (tenantid) references tenants (tenantid) )  create table customers (     customerid bigint identity(1,1),     tenantid   bigint,     primary key (customerid),     unique key (tenantid, customerid),     foreign key (tenantid) references tenants (tenantid) )  create table jobs (     jobid      bigint identity(1,1),     tenantid   bigint,     shopid     bigint,     customerid bigint,     primary key (jobid),     foreign key (tenantid, shopid) references shops (tenantid, shopid),     foreign key (tenantid, customerid) references customers (tenantid, customerid) ) 

if you're concerned storage space, suggest calculate actual cost of space based on realistic volumes of data , benchmark performance differences between fk constraints vs triggers vs check constraints involving subquery. don't assume attribute inefficient.