This may be a dumb question, but I can't seem to figure a resolution for what looks to be a circular reference.
I'm working on a model that makes use of heavy mutually inclusive subtyping. This in itself has lead to many issues, but that's another story.
One of my issues has to do with what looks to be a circular reference.
I have a main table named Participant. This table is the top level representation of all "actors" that interact with our system. I also have tables representing other generic entities of "Document" and "Application". I have a one to many relationship between Participant and Application where Application has a primary surrogate key of Application_ID with a Non identifying FK from Participant. A participant can have one or more applcations. I also have an associative table called "Participant_Document" which resolved the relationship of A given participant can have one or more document and a document can be associated to one or more participants. So the primary key is the composite key of Participant_ID and Document_ID.
Here is where I have the problem. I want to have an ability to relate an Application to a Document. An application can be associated to one or more Documents. So my instinct is to make an associative table between "Participant_Document" and "Application" resulting in "Participant_Document_Application". Here is where the circular reference occurs. Now I have a two references to "Participant_ID" one in the form of a PK reference from "Participant_Document" and one from a Non-Identifying FK in "Application".
Is this wrong? It seems that it would be possible to associate two different Participant_ID's resulting in an incorrect record? I'd appreciate any suggestions or help.
I don't see anything wrong with what you have. Your issue is that you have a rule that the Application in Participant_document_application must belong to the same Participant as the Participant_Document does. In standard SQL this could be enforced by a check constraint or an assertion, but unfortunately not in Oracle, which only allows very limited singe-row, single-table check constraints.
You could enforce this rule via triggers, or by changing the design as follows:
1) Create a UNIQUE constraint on Application (Participant_ID, Application_ID) - this is redundant since Apllication_ID is the primary key, but helps us here.
2) Modify the foreign key from Participant_document_application to Application to reference the new UNIQUE constraint, i.e. to include the Participant_ID column that was inherited from Participant_document.
The rule is now enforced!
Alternatively for step 2, you could add an additional column Application_Participant_ID to the table and foreign key, and then have a check constraint to ensure that (Application_Participant_ID = Participant_ID).
Thank you for the input. I was going down the path of solution one in my thought process, but I wasn't sure if it could be done.
I'm not clear on how the FK relationships would ensure the two references to Participant_ID are equal. In solution one, when you say create an FK from the Application table with the new ApplicationI_ID, Participant_ID composite key, would that FK reference the composite primary of the new Participant_Application_Document key? The Participant_ID has already been migrated from the primary composite key of Participant_Document, I don't know if Erwin will allow for the same attribute to have more than one FK constraint from two different parents?
If I misunderstood this and what you meant to convey is that the Participant_ID from one of the parent tables would be referenced as a non_identifying FK, how to do enforce that the Identifying and Non_identifying keys are the same? Are you suggesting creating a attribute level constraint to enforce this rule?
Why do you want to reference Participant_Document in the relationship between Document and Application? Is this new relation one to many, or many to many? Your model will be more understandable and extensible in the future if you keep associations distinct. Indeed, the question you raised is evidence of this point. Your establishing the schema now and see the possibility of confusion in relational integrity. What will happen in the future, after the schema has aged and been extended, and the need arises to revisit this relationship? I suspect another DBA would be confused also. Although it may seem like overkill, keeping associations in separate tables avoides a multitude of issues later.
Here you have one column participant_document_application.participant_id that takes part in 2 foreign keys. The rule that the participant_id must be the same in both foreign keys is enforced by the fact that the column is common to both FKs.
I don't know whether Erwin allows this or not, but Oracle certainly does!
Why do you want to reference Participant_Document in the relationship between Document and Application? Is this new relation one to many, or many to many?
For each application, there can be one or more documents. A document can only refer to one application. one to many.
Originally Posted by brevert
Your model will be more understandable and extensible in the future if you keep associations distinct. Indeed, the question you raised is evidence of this point. Your establishing the schema now and see the possibility of confusion in relational integrity. What will happen in the future, after the schema has aged and been extended, and the need arises to revisit this relationship? I suspect another DBA would be confused also. Although it may seem like overkill, keeping associations in separate tables avoides a multitude of issues later.
Well, I'm the 3rd modeler to inherit this stinker , all 277 tables, small but complex! I believe the root of the problem has to do with the fact that this model is based on the idea using one unique key to represent a business "participant". That in itself wouldn't be so bad, but in addition to this requirement, the business needs to allow each "Participant" to have multiple "Roles" meaning a Participant can't be represented by use of mutually exclusive subtypes Also note that these roles can be added to a participant as time goes on. Due to this issue, and convoluted business rules, I'm running into complex data representations which I'm not sure are due to a modeling or business rule flaw.