How to relate 2 tables to a third when the first 2 tables have a relationship?

Question

For once, I find myself at an utter loss in terms of coming up with a good question title.

Consider the three following tables. Each has additional columns, but only the columns relevant to the relationships between the tables are mentioned:

• Table a has a primary key a_id
• Table b has a primary key b_id and a column a_id which is a foreign key to a (one-to-many relationship)
• Table reference has a primary key ref_id

reference is a list of sources that are cited to substantiate information contained in the other 2 tables. As such, I need tables to relate them together. Both a and b have many-to-many relationships with reference, but there is an additional complication: a footnote number. This footnote number must be unique for each a and reference pair, but it is only required when a relationship between a b and the same reference exists. (Of course, that b must be related to the same a.) The best way I have come up with to represent this relationship so far is a pair of tables:

bridge_a_reference:

• Columns: a_id, ref_id, footnote_num (nullable)
• Primary key: a_id, ref_id
• Foreign keys: a_id to table a; ref_id to table reference

bridge_b_reference:

• Columns: b_id, a_id, ref_id
• Primary key: b_id, ref_id
• Foreign keys: b_id, a_id to table b; a_id, ref_id to table bridge_a_reference

This ensures data consistency. There can only be a relationship between b and reference if a corresponding relationship between a and reference exists and b is related to the correct a. It also ensures that the footnote number will be consistent for all a/reference pairs. However, it's redundant, storing the relationship between a and b a second time (though enforced to be consistent, at least), and it doesn't ensure the footnote number is required for the b/reference relationships.

I've tried rearranging the tables in my head a few times, but everything else I can come up allows some kind of inconsistency (b to reference relationships without the corresponding a to reference or b to a relationship or inconsistent footnote numbers for the a/reference pairs.) How can I better structure my bridge tables to eliminate redundancy and ensure the footnote requirements? Is it even possible?

Answer 1

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