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

[former_member988926]

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?

[Former Member · Accepted solution]

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.

[GeorgeMcGeachie · Accepted solution]

Implemented correctly, transformations can also be run on-demand, from the Tools menu. You need to make sure they're defined as 'post-generation', so they always operate on a PDM (not a LDM). A model check could call some of the same code as the Transformation.

[former_member988926 · Accepted solution]

correct, I only want the SURROGATE KEYS present in the PDM, so post-generation is the way to go.  Unfortunately, my current client uses Erwin, so I am struggling to implement basic inheritance with this piece-o-crap tool.  SURROGATE KEY generation is the least of my issues. LOL

[GeorgeMcGeachie · Accepted solution]

Wasn't ERwin voted 'best data modelling tool' by DBAs recently? They obviously don't know what they're missing .

[former_member988926 · Accepted solution]

this says it all...

Roll Up/ Roll Down Super type/ subtypes | CA Communities

..even with all of it's shortcomings, PowerDesigner is still my favorite modelling tool to-date.  the ability to preview inheritance roll-up/roll-down in real time makes it the one feature that sets it apart.  not even ER Studio supports this real-time feature.

[GeorgeMcGeachie · Accepted solution]

In ER/Studio you have to generate all the parent and child tables into the PDM then rollup or rolldown to sort them out in the PDM.

I found an interesting bug in ER/Studio last year. In the PDM, I rolled up some sub-types into the super-type table, leaving some of the sub-types behind; the remaining sub-type tables no longer had FKS from the super-type table. Ouch. They told me it would be fixed later in the year, never checked if they did.

[former_member988926 · Accepted solution]

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.

[Former Member]

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

[GeorgeMcGeachie]

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 .

[Former Member]

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