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?
Best Answer
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:We add this table - which will basically store only those rows from
a_ref
with footnote, those you want to add children into theb_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
):