09 May 1996 updated
Can you explain the thinking behind how a CDM one-to-one generates into the PDM? We are curious about the generation of the reverse FK and would appreciate your explanation.
Your question makes sense. The above schema means:
This implies :
To implement these rules, we have 2 solutions :
We have chosen the solution 1) because it's similar to the (0,1 to 0,1) and (1,1 to 1,1) situations. Otherwise, we need an option to decide which FK we should generate. However, the (0,1 to 1,1) situation may be treated as a special case. If everybody thinks it's better to generate only one FK, may change the PDM generation. Xiao
People with whom we have spoken prefer treat all one-to-one relationships as a sub-set of one-to-many. A one-to-one can be handled like a one-to-many with only one "child" ccurrence. Aside from the need to identify or select which end is designated as "parent", this is within the standard relational model with a single FK in the "child" table. Otherwise, symetrical FKs require specialized transaction and/or trigger logic to maintain bidirectional FK synchronization.
By the way, can you explain what you mean by: "We have chosen the solution 1) because it's similar to the (0,1 to 0,1) and (1,1 to 1,1) situations." First, PowerDesigner prohibits the (1,1 to 1,1) form. Second, as far as we can see you currently treat (0,1 to 0,1) no differently from (1,1 to 0,1). Are we missing something?
You are right, it's not possible to define 1,1 to 1,1 relationship. For a 0,1 to 0,1 relationship, we can't decide which foreign key we should generate. We need an extra option to choose the direction. For a 1,1 to 0,1 relationship, we could keep only one foreign key. What do you prefer ? If it makes sense, I could change the generation.
The relationship definition could have an additional property to indicate the "parent" entity (i.e., the source of the foreign key). A relationship which is 1,1 to 0,1 (or 0,1 to 1,1) implies that the 1,1 end is the "parent". Only the form of 0,1 to 0,1 is ambiguous and would thus require and enable this new user choice, which should default to the first entity attached to such a relationship.
In any case, if I am not mistaken, any 1-to-1 relationship in PowerDesigner is only conceptual because you currently do not implement trigger code to limit the number of "child" rows to <=1. This could be done, of course, in the insert trigger by selecting on the FK and checking for a count of zero. I would suggest that you add such trigger code to the templates because it is not apparent to users that this 1-to-1 feature is only conceptual and does not generate any limit.
A customer has pointed out to us that there is a work-around to circumvent PowerDesigner's prohibition on mandatory one-to-one. Create a many-to-many; make it mandatory at both ends; convert both ends to one-to-one. Voila - a one-to-one which is mandatory at both ends! Of course we would argure that this is a physical segmentation rather than a conceptual design.