DDL Transformation Column Duplication

[pvickers]

I have a question regarding the built-in DDL transformation template.

The simple scenario...

Imagine you have a simple logical data model with two tables... The first table is named "Parent" and the second table is named "Child".
The "Parent" table has  a column named ParentID which is defined as the primary key identifier.
The "Child" table has a column named "ChildID" which is defined as the primary key identifier.  The "Child" table has an additional column named "ParentID" which is defined as a foreign key to the "Parent" table.

For clarification - The relationships are defined in the logical model including the primary and foreign key columns.

The elements described above have been modeled using the stereotype of "table" and have their target DBMS set to "SQL Server 2012".
The relationship from "Child" to "Parent" is mandatory...In other words, the "Parent" is mandatory if you wish to store a row in "Child".

The 2 problems I'm experiencing are...
1. After performing the DDL transformation, Sparx EA creates new columns in the physical model for the primary and foreign key columns rather than using the columns already defined in the logical model.
2. Sparx EA transforms the foreign key column in the "Child" table as optional, even when it is defined as mandatory in the logical model.

I know that the DDL transformation is designed to create columns for the primary and foreign keys from logical models, but it should be capable of using existing columns when they have already been defined.

I'm quite certain this worked properly in earlier versions of Sparx EA.

Thanks!

[Paolo F Cantoni]

Hi P,

It's not just the DDL transformations.  IIRC, I believe if you try to add a new Foreign Key Constraint relationship in "real" tables you'll get the same effect.

To some extent, I can understand the rationale for this.  Check the documentation for Foreign Key Constraints in normal Data Modelling to see if it's documented.

HTH,
Paolo

[pvickers]

Thanks for your reply Paolo.

After a fresh look at the documentation I did not find the enlightenment I was seeking.

I forgot to mention in my original post that I am running these tests on Sparx EA 15.1 build 1529.

Do you or does anyone else recall if this worked correctly in a previous version of EA?

I'm thinking of re-installing version 14 and recovering the DDL transformation template from it to try in 15.1.

Regards,
Perry

[ducatiross]

Hi Perry,

We have found this too, and decided to remove the attributes from the logical entities that reference the key fields, and instead let EA generate them using the relationships.

Matthew

[Sunshine]

Probably best not to limit your thinking in terms of data models only but in object models where you have a platform independent model (PIM) that can be transformed into a platform dependent model (PDM) were the transforms were originally designed to operate. The  transforms can create PDMs such as C#, Java, Physical Data model etc where primary keys aren't used in C# or Java so shouldn't appear in the PIM.
Hope that helps put a little more context into the concept of model driven development.