Composite keys : Problem when Generate CDM to LDM

[Former Member]

Hi,

PowerDesigner 16.5.4.1 (4535) SP04 PL01 - 64 bits

A) I have included with this ticket a conceptual model as an example. After download the file remove " SAP.mcd.txt"

B) In this model we have five entities and the primary keys of the following entities are composite keys :

• "Inscription activité",
• "Activité club",
• "Membre club".

Note: The customer can't set up simple keys for these entities.

C) When generating the logical model for "Inscription activité" entity we need to get this composite key :

• "No_inscription_activité",
• "No_membre",
• "Numéro_activité",
• "Code_club"

D) As you can see PowerDesigner add attribute "Act_Code_club." for the composite key of "Inscription activité" entity. This attribute is a foreign key from the entity "Activité_club."

E) Given that the attribute "Act_Code_club" is the same information as the attribute "Code_club.". We click on the relationship name "reserve" and we change the child attribute "Act_Code_Club" for "Code_club."

We click on "Ok".

The composite key displayed is now correct.

Note: Act => Are the three (3) first characters of the name of the table ("Activité_club". The prefix is added to Code_club when the key is migrated. Giving "Act_Code_club".

F) We return to the conceptual model and we add for the entity "Person", the attribute "Sexe" char (1). We update the existing logical model generated previously while we have checked Preserve modifications.

G) All is adequate

H) We return to the conceptual model and we add for the entity "Person", the attribute "Initial" char (5). We update the existing logical model generated previously while we have checked Preserve modifications.

I) As you can see PowerDesigner add the attribute "Act_Code_club" in the composite key of "Inscription activité" entity.

Conclusion: If we update two times a logical model PowerDesigner flaw our work.

Do you have an idea to fix this problem?

Gestion club select.mcd SAP.mcd.txt.zip

[GeorgeMcGeachie · Accepted solution]

There does seem to be a bug here, Benoit. I followed the steps you outlined, with the same result.

The first update did not pre-select the attribute change in 'Inscription activité', but the second update DID pre-select it, though it did not pre-select the change to the join. After the second update, the LDM entity 'Inscription activité' contains two 'Code Club' attributes, and only the original one is referenced in joins.

Going back to your original assertion though, that [the attribute "Act_Code_club" is the same information as the attribute "Code_club"]. Excuseme, but I must disagree - it is not the same information, "Code_club" is the identifier of the Club inherited via the rempli and possède relationships, whereas "Act_Code_club" is the identifier of the Club that is inherited via the réserve and organise relationships. I assume you expect the two relationship chains to link to the same Club, which is an integrity constraint linking the two relationships. You can avoid this situation by changing your CDM slightly - change the parent entity of 'rempli' from 'Membre club' to 'Personne'. It is a person (not a Membre) who signs up for a club activity, and part of the validation for the relationship is that the person must be allowed to do that, probably by being a current member of the club, which can easily be tested by finding the person in 'Membre Club'. That will also allow you to support possible arrangements between clubs, where a member of club A can sign up for activities in club B.

[Former Member · Accepted solution]

There does seem to be a bug here.

So I will open a ticket with SAP

Benoit. I followed the steps you outlined, with the same result.

You are completely right I expect the two relationship chains to link to the same Club, which is an integrity constraint linking the two relationships. Your comment will allow me to better clarify the issue with SAP.

You can avoid this situation by changing your CDM slightly

i understand your suggestion. But the client have this problem for many others situations.

Thank you,

[GeorgeMcGeachie · Accepted solution]

Now I wish I hadn't replied quite so quickly . My solution didn't allow you to record which membership a person used to book an activity, which you would probably need to know in a multi-club system. So I wish to change my mind, as all good modellers do when they've re-assessed requirements. I suggest that you leave the CDM as it is, and do not amend the relationship joins in the LDM, so you have two instances of 'Code_club' in 'Inscription activité'. Now add a Business Rule to link the two 'Code_club' attributes, setting the type of rule to 'Constraint'. You'll be able to use this in the PDM to generate a constraint.

I did try linking the two relationships to a business rule in the CDM, but I had a problem when I generated the LDM - the business rule was generated, but the dependencies between the rule and relationships were not generated. (generating from LDM to PDM was OK though)

[Former Member · Accepted solution]

Here's another workaround that I have tried and seems to work:

On the second regeneration, you can see that the logical attribute is being added back.  If you uncheck this change in the compare screen, and continue, the logical attribute will not be recreated, and the logical model will be as you wanted it to be.

Once you have done this the first time (on the second re-generation) the choice will be preserved for all future regenerations.  It's not a solution, but it is a workaround you can use that will reduce the frequency of this happening in synchronizations between your CDM and LDM.

[Former Member · Accepted solution]

Hi David,

Good idea, unfortunately the Workaround does not work.

So I uncheck the addition of the field "Act_Code_club" at attributes and PK level when the compare screen pop-up.

I am able to perform second update, third update.

Unfortunately the fourth update the problem returns.

Given that client do extensive use of composite keys that has several models and the problem back repeatedly. The client can't use this workaround or the mine as indicated in my request above without affecting the result of the generated models.

As you know the compare screen show many modifications usually when we do modelling.

Thank,

[former_member192453 · Accepted solution]

Hi Benoit,

This has been submitted as a bug on CR 778646. This CR is scheduled to be fixed in the next PL for PD 16.5.4.

Regards,

Anthony