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 keya_id
- Table
b
has a primary keyb_id
and a columna_id
which is a foreign key toa
(one-to-many relationship) - Table
reference
has a primary keyref_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 tablea
;ref_id
to tablereference
bridge_b_reference
:
- Columns:
b_id
,a_id
,ref_id
- Primary key:
b_id
,ref_id
- Foreign keys:
b_id
,a_id
to tableb
;a_id
,ref_id
to tablebridge_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?
footnote_sum
(asNOT NULL
) to tablebridge_b_reference
and then modify the FK to(a_id, ref_id, footnote_sum)
to tablebridge_a_reference
?a
/reference
pairs that do not have a correspondingb
/reference
pair, and those entries should not have a footnote number.NULL
in that table (bridge_a_reference
). but when you want to add a related row in thebridge_b_reference
table, you'd have (because of the FK) to modify first the row in thebridge_a_reference
and assign a value to that null.b
/reference
table, but at the cost of more redundancy.