Foreign key migration

From: Russ <rgoring_at_sbcglobal.net>
Date: 29 Nov 2001 20:08:12 -0800
Message-ID: <9d329c10.0111292008.45a35719_at_posting.google.com>

I have a question related to migration of foreign keys to dependent tables. (Although this post is rather long, the entities are relations involved are not complex - I've included the DDL below for reference.)

Here are the basic predicates.

Documents are assigned one and only one template. Templates have one or more details.
Documents have values (details) associated with the details of their assigned template.

My question is related to the design of the DOCUMENT_DETAIL table and whether or not to migrate the template_id key from the DOCUMENT table.

Note that template_id is not necessary to establish uniqueness for document details. In other words, the template_id is already defined in the DOCUMENT_TABLE so it is redundant to include it in the detail table. (This is shown in DOCUMENT_DETAIL_OPTION_A.)

On the other hand, failure to migrate template_id greatly increases the complexity of checking the validity of the template_id/template_detail_id pair. A trigger must be used to join back to the DOCUMENT table to establish a foreign key pair to compare to the TEMPLATE_DETAIL table.

If the template_id key is migrated (as shown in DOCUMENT_DETAIL_OPTION_B), it becomes very easy to set up a foreign key constraint to the TEMPLATE_DETAIL_TABLE. However, the tables are no longer normalized.

Is appears as if in this case denormalization actually makes integrity checking easier. This is quite the opposite of conventional wisdom. Is this a correct assessment? Is there another solution?

FYI, I ran across this situation in a production database. This solution as implemented was to not migrate the key and rely solely upon the application to enforce referential integrity.

Any thoughts on this matter would be appreciated.

CREATE TABLE Template (

       Template_Id          VARCHAR2(20) NOT NULL,
       PRIMARY KEY (Template_Id)

);

CREATE TABLE Template_Detail (

       Template_Id          VARCHAR2(20) NOT NULL,
       Template_Detail_Id   VARCHAR2(20) NOT NULL,
       PRIMARY KEY (Template_Id, Template_Detail_Id),
       FOREIGN KEY (Template_Id) REFERENCES Template

);

CREATE TABLE Document (

       Document_Id          VARCHAR2(20) NOT NULL,
       Template_Id          VARCHAR2(20) NOT NULL,
       PRIMARY KEY (Document_Id),
       FOREIGN KEY (Template_Id) REFERENCES Template

);

CREATE TABLE Document_Detail_Option_A (

       Document_Id          VARCHAR2(20) NOT NULL,
       Template_Detail_Id   VARCHAR2(20) NOT NULL,
       Value                VARCHAR2(20) NOT NULL,
       PRIMARY KEY (Document_Id, Template_Detail_Id),
       FOREIGN KEY (Document_Id) REFERENCES Document

);

CREATE TABLE Document_Detail_Option_B (

       Document_Id          VARCHAR2(20) NOT NULL,
       Template_Id          VARCHAR2(20) NOT NULL,
       Template_Detail_Id   VARCHAR2(20) NOT NULL,
       Value                VARCHAR2(20) NOT NULL,
       PRIMARY KEY (Document_Id, Template_Id, Template_Detail_Id),
       FOREIGN KEY (Document_Id) REFERENCES Document,
       FOREIGN KEY (Template_Id, Template_Detail_Id) REFERENCES

Template_Detail
);
Received on Fri Nov 30 2001 - 05:08:12 CET