Here is a suggestion so you can enforce the constraints you want declaratively. (I've simplified the table names a bit, removed the bridge_ prefix.)
We remove footnote_num from:
Table: a_ref -- was named: bridge_a_reference
a_id,
ref_id
Primary key:
(a_id, ref_id)
Foreign keys:
a_id -> a
ref_id -> reference
We add this table - which will basically store only those rows from a_ref with footnote, those you want to add children into the b_ref:
Table: a_ref_with_footnote
a_id,
ref_id,
footnote_num
Primary key:
(a_id, ref_id)
Unique key:
(a_id, footnote_num)
Foreign keys:
(a_id, ref_id) -> a_ref
And finally the 3rd table stays as in your design except the foreign keys which now reference the intermediate table (a_ref_with_footnote):
Table: b_ref -- was named: bridge_b_reference:
a_id,
b_id,
ref_id,
Primary key:
(b_id, ref_id)
Foreign keys:
(a_id, b_id) -> b
(a_id, ref_id) -> a_ref_with_footnote
No, you should not have multiple State tables. Having a single table with relationships to the others is the right way to approach this. The perception that this somehow implies that the other tables are linked, other than they share a State, is purely in your head - such an interpretation would not be common practice.
You should distinguish between your logical data model and your physical database design. I would recommend adding the relationships to State into the logical model. You may choose to omit the foreign key constraints from your database for performance or other reasons.
There's a post on Dell's Toad site which I can't find just now. It's about a 200 table DB, every one of which has a UpdateUserId column and corresponding FK relationship to the User table. The plan for a DELETE User.. statement is a thing of beauty - a perfect left-deep triangle of 200 joins! They would have been better without the constraint.
Edit: here's the link. I mis-remembered the details but the principle's the same.
Best Answer
An intersection table such as you've described is the standard way to implement a many to many relationship in a physical relational database.
The intersection table has foreign keys to each of the parent tables that it's joining. These may be simple or compound foreign keys. That doesn't matter.
The primary key of the intersection table is the combination of the two foreign keys to the parent tables. For this reason, the primary key of an intersection table is always a compound foreign key.