E2L: How do you handle Foreign Keys that participate in multiple relationships?

Question

I have a Database model like this

FlowObject
FlowObjectID (PK)
Description
Active

ProcessObject
FlowObjectID (PK, FK)
HasSubmit

DecisionObject
FlowObjectID (PK, FK)
YesFlowObjectID (FK)
NoFlowObjectID (FK)
YesCaption
NoCaption

When I try and use create my Entity model I get this warning in my project.

Foreign Key constraint 'FK_ProcessObject_FlowObject1' has been omitted from the storage model. Column 'FlowObjectID' of table 'Investigations.Store.ProcessObject' is a Foreign Key participating in multiple relationships. A one-to-one Entity Model will not validate since data inconsistency is possible.

???

Why did it drop my foreign key? Because "A one-to-one Entity Model will not validate since data inconsistency is possible."

So it sounds like it is saying it dropped the FK because of data inconsistency but dropping the FK actually reduces date consistency?

Should I redesign my database? Is there anyway for L2E to handle FK's that participate in multiple relationships? Is it considered bad database design to have FK's that participate in multiple relationships?

Answer 1

What you've described, translated to object-oriented terms, is that a FlowObject contains an optional ProcessObject and an optional DecisionObject. If this is what you actually meant, the database schema is correct.

If you're trying to have ProcessObject and DecisionObject extend FlowObject, inconsistency is possible because both the ProcessObject and DecisionObject rows may exist. To eliminate the inconsistency, the union-subclass modeling technique is appropriate: only ProcessObject and DecisionObject tables exist, each containing all relevant fields, and FlowObject, as an abstract base class, becomes a view consisting of the union of the common base fields between the two tables.