0

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?

  • How about adding the footnote_sum (as NOT NULL) to table bridge_b_reference and then modify the FK to (a_id, ref_id, footnote_sum) to table bridge_a_reference ? – ypercubeᵀᴹ Jan 21 '14 at 17:26
  • @ypercube There are a/reference pairs that do not have a corresponding b/reference pair, and those entries should not have a footnote number. – jpmc26 Jan 21 '14 at 17:28
  • Those would still have NULL in that table (bridge_a_reference). but when you want to add a related row in the bridge_b_reference table, you'd have (because of the FK) to modify first the row in the bridge_a_reference and assign a value to that null. – ypercubeᵀᴹ Jan 21 '14 at 17:28
  • @ypercube Oh, I misunderstood. I see. That would ensure that the footnote number is required for the b/reference table, but at the cost of more redundancy. – jpmc26 Jan 21 '14 at 17:30
  • I think it can be improved a bit. Let me form an answer. Can you clarify what you mean with "unique" in the sentence: "This footnote number must be unique for each a and reference pair," – ypercubeᵀᴹ Jan 21 '14 at 17:31
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
  • I decided to go with a small variation of this. I eliminated the foreign key from a_ref_with_footnote to a_ref, and I created a view that combines it with the a_ref table to get a complete list of all the references associated with a row in a directly or indirectly. This way, entries in a_ref are not required for entries in b_ref, which makes things a little looser. – jpmc26 Jan 22 '14 at 19:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.