Relational design - one "many to many" and two "one to many" "between" two tables

Question

I have the following (simplified) app requirement for which I need to create a database in SQL Server:

• entity A is in a many to many relationship with entity B
• entity A is in a one to many relationship with entity C
• entity B is in a one to many relationship with entity C as well.

I modeled this with the following tables:

A (a PK)

B (b PK)

A_B(a FK A(a), b FK B(b), PK(a,b))

C (c PK, a FK A(a), b FK B(b))

Obs. From the (extended) spec: one can have a pair (a,b) in A_B that doesn't exist in C.

1. Is this a good design? I'm asking because obviously you can insert a pair (a,b) in table C that doesn't exist in table A_B, the latter being responsible for associating As to Bs.

2. One could create a trigger on C to do the above check, but this solution would be at the implementation level.

3. Another option would be to create a composite FK on (a,b) in C. I never used them before, so again my question would if is it ok to proceed like this.

Thanks for any hints!

Answer 1

Yes, a foreign key from C to A_B will prevent entries appearing in C that don't appear in A_B. Something like:

 CREATE TABLE A (a char(3) not null, constraint PK_A PRIMARY KEY (a));

 CREATE TABLE B (b char(7) not null, constraint PK_B PRIMARY KEY (b));

 CREATE TABLE A_B(
    a char(3) not null,
    b char(7) not null,
    constraint PK_A_B PRIMARY KEY(a,b),
    constraint FK_A_B_A FOREIGN KEY (a) REFERENCES A(a),
    constraint FK_A_B_B FOREIGN KEY (b) REFERENCES B(b)
 );

 CREATE TABLE C (
    c char(19) not null,
    a char(3) not null,
    b char(7) not null,
    constraint PK_C PRIMARY KEY (c),
    constraint FK_C_A FOREIGN KEY (a) REFERENCES A(a),
    constraint FK_C_B FOREIGN KEY (b) REFERENCES B(b),
    constraint FK_C_A_B FOREIGN KEY (a,b) REFERENCES A_B(a,b)
 );

It's up to you whether you continue to have FK_C_A and FK_C_B. In the above model, they're redundant, but if, say, a and/or b in C is nullable then it makes sense to keep them separately to enforce the foreign keys when the values aren't all non-NULL.