locked
inheritance from multiple tables into shared table RRS feed

  • Question

  • I'm attempting to store attributes common to 2 entities into 1 table (Table C) as per below diagram.  The problem is that I can't maintain Referential Integrity.  The Table C common attributes are enrichment data (i.e., data gets added to the base records in Tables A and B during a subsequent vetting/enrichment process).  The cardinality between Table A and Table C is 1:1, and likewise between Table B and Table C.  Is there a way to structurally handle this situation and still maintain Referential Integrity?

    Thanks,

    John 

    Wednesday, May 29, 2013 5:20 PM

Answers

  • I did some more thinking and realized I was trying to force an unworthy design onto the database.  I guess that's the good thing about rules - if you're breaking RI, it probably means you're doing it wrong!

    Table C, in terms of when the data is entered, comes after data entry for data in Tables A and B, thus it's essentially enrichment data.  BUT, that mislead me to think it was also structurally / formally derivative as compared with Tables A and B.  Meaning I thought the primary keys from A and B had to feed into C as a shared foreign key.

    I had it backwards!

    In fact, since Table C has purview of sorts over both Table A and B, then it's Table C's PK which needs to propagate into Table A and Table B as a foreign key.  So according to the diagram, I need to reverse the arrows and drop the [EntityName] fields.  Much simpler - but it wasn't so intuitive.  And now I can enforce Referential Integrity!

    • Marked as answer by DimDude Thursday, May 30, 2013 1:31 AM
    Thursday, May 30, 2013 1:30 AM

All replies

  • you cannot have a foreign key that references multiple primary keys on different tables. You can either create a trigger to enforce this or write a stored procedure and manage your insert through the stored procedure which take care of this validation.
    • Marked as answer by DimDude Wednesday, May 29, 2013 6:07 PM
    • Unmarked as answer by DimDude Thursday, May 30, 2013 1:14 AM
    Wednesday, May 29, 2013 5:35 PM
  • Thanks Sanoj - I was afraid of that.  I thought maybe there was some wrinkle of Normalization I was overlooking, but perhaps Normalization doesn't account for this "shared attributes" situation...
    Wednesday, May 29, 2013 5:45 PM
  • I did some more thinking and realized I was trying to force an unworthy design onto the database.  I guess that's the good thing about rules - if you're breaking RI, it probably means you're doing it wrong!

    Table C, in terms of when the data is entered, comes after data entry for data in Tables A and B, thus it's essentially enrichment data.  BUT, that mislead me to think it was also structurally / formally derivative as compared with Tables A and B.  Meaning I thought the primary keys from A and B had to feed into C as a shared foreign key.

    I had it backwards!

    In fact, since Table C has purview of sorts over both Table A and B, then it's Table C's PK which needs to propagate into Table A and Table B as a foreign key.  So according to the diagram, I need to reverse the arrows and drop the [EntityName] fields.  Much simpler - but it wasn't so intuitive.  And now I can enforce Referential Integrity!

    • Marked as answer by DimDude Thursday, May 30, 2013 1:31 AM
    Thursday, May 30, 2013 1:30 AM