Skip to Content

convert NATURAL KEYS in my LDM into SURROGATE KEYS when I generate my PDM?

PowerDesigner 16.5

My logical data model (LDM) contains only natural keys (NKs). All of my LDM relationships reference NKs. When generating my physical data model (PDM), I wand a surrogate key added to every TABLE and I want all of the NK relationships to be replaced with FK relationships based on the newly generated surrogate keys. Is there a way of configuring my PDM generation options so that this is done automatically?

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Aug 17, 2016 at 09:07 AM

    There are two approaches how you can solve it. First is (as George mentioned), create an extension and implement your transformation there. This will work only at the time of generating model from LDM to PDM.

    The second approach is more appropriate if you want to apply this rule/policy repeatedly in your PDM. You can implement this functionality into Custom check (with autocorrection enabled) or perhaps implement it as validation event handler on the table level. But this second approach is more complex, than it seems for the first sight. You will have to deal with more situations like this: What should happen to the surrogate key, whe the Reference properties changes? How should the handler deal when you change/modify the key? etc.

    Add a comment
    10|10000 characters needed characters exceeded

    • is it any wonder that so many organizations attempt the application layer approach to maintaining domain integrity (of their data), when you see just what inferior tools data modelers have to work with (as compared to application developers)?

      problem being, vendors (and not just data-modeling tool vendors) would rather concentrate on adding marketable features to their products rather than concentrating on core functionality. Until we address this trend, we are going to see more and more name-value pair database designs which are dominated by "thing" tables.

  • author's profile photo Former Member
    Former Member
    Posted on Oct 10, 2014 at 05:39 PM

    Good question. I think it is by extending transformation. I wonder to know how work with extensions to customize model generations.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 16, 2016 at 04:40 PM

    I'd like a better answer to this too.

    I've tried a couple of ways that are not altogether satisfactory.

    1. Add the surrogate to the LDM

    OR 2. If the Natural Key is just one attribute, Over ride the name and data type in the PDM manually. (at least the mapping is kept)

    3. Add the new surrogate attribute in the PDM and point the PK and FKs to it (i.e. change the Join column on the FK)

    As I said. These are all fiddly, error-prone ways to do it.

    Jane

    Add a comment
    10|10000 characters needed characters exceeded

    • You can definitely do this with a Transformation, a script, or a custom model check (the auto-fix would create the required key for you). The script would be relatively straightforward, I reckon. A little experimentation would be needed to see what happens when you add a new PK to a table.

      Automation would make it less risky, assuming your logic deals with all eventualities, of course 😊.

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.