Overlaying configurable business logic into a physical data model

[Modesto Vega]

I am working with a large reverse engineered physical data model without any foreign keys. This is a very common design characteristics of systems offering a business logic configuration capability - e.g., ERP systems.

We know where the business logic is stored and would like to create relationships between the tables to document the business logic without actually creating any new database objects, such as foreign keys. If we create associations between tables, Sparx EA will create additional database objects, representing foreign keys, that we do not want in the model. We have tried prefixing the objects with something to identify them as "derived" but found this unsatisfactory.

Is there an easy workaround that I am missing?

 

[Geert Bellekens]

We have used dependencies to document relations between tables for systems that don't have "real" foreign keys defined.

Geert

[Paolo F Cantoni]

Hi Modesto,
As you may recall, we've taken the "two physical models" approach.  We reverse engineer the DB into the implementation model.  We then clone that to the Design model, which has all the enrichment we require.  This allows us to continue reverse-engineering the Implementation model without (directly) affecting the Design model.  The Design model has to track changes to the Implementation model, but that isn't too onerous.


If you search for posts on "Design Model" by me you'll see some of our earlier discussions.


HTH,
Paolo

[Richard Freggi]

I do the same as Paolo - a real physical reverse engineering schema model and a clone (I use logical level for the clone since there is no benefit to include the physical detail).
For views that don't have keys or a real relationship to the tables, I use dependencies.  It's debatable if it's OK to show a dependency between view and queried tables in a physical data model, but it's definitely no issue in a logical model.

Then, I represent the logic transformations (usually they are external scripts that read/write to the database, like Python, .net etc.) with procedures and functions (theoretically I could use artifacts but never felt the need).  These are not real but again, OK at logical level.  I always postfix the name of these non-physical objects to differentiate them from the actual database object: even at logical level, I use a postfix to indicate that the object is just a convenient representation.  I also add some explanation in the comment fields.

I use associations to tables and views show which objects are read/written by the procedures etc.

When forward engineering to physical level, it's up to the data engineer to decide what to do with the prefixed object (implement outside the database schema, implement as a new procedure, as a lookup table, trigger etc.).  Works well for me, it's clean, maintainable/scalable etc.

[Modesto Vega]

Dependencies are not  a very orthodox way of modelling this, I will prefer to use associations - please see below more comments on associations. However, for this specific exercise we are currently using dependencies whilst we figure out another way of doing this.

I know we have discussed a similar subject before, with version 13, but cannot find the thread.

Using the "two model" approach, either a physical + logical model or 2 physical models, presents a number of challenges for the exercise at hand. The challenges are a result of the size of the physical model, the fact that the physical to logical translation for this model is not obvious/trivial, and some Sparx "limitations".
1) Sparx EA does not have an easy out-of-the-box way of copying or transforming a model into another model with trace/lineage between entities [Note: I know I could write a transform, I have done it in the past, or a script but are short of time.]
2) AFIK it is not possible to draw an association between 2 classes with more than one attribute in the source and target roles. This is a very significant limitation. I think this is not supported by the UML specification.

The easiest way to do what we are after with this model will be for Sparx to support a type of "softer" association between tables that does 1) not result in the creation of a foreign key object, and 2) require a unique index in the source table (although this is less of a problem). In my simple mind, this should be easy to achieve as it just entails Sparx Systems adding an extra property to the association connector between tables.

Also, I am starting to think that it would have been better if Sparx had implemented constrains (FK,s, PKsa and so on) and indexes as elements. I am still searching for them in the database.

[qwerty]

FK and PK are stereotyped attributes. So they are in t_attribute (probably along with t_xref for the stereotype).

q.

[Geert Bellekens]

FK and PK are stereotyped attributes. So they are in t_attribute (probably along with t_xref for the stereotype).

q.

It's actually more complicated than that.

A foreign key is defined by a combination of
- the association
- The FK constraint opteration on the class
- The stereotypes on the columns involved.

Geert

[qwerty]

Geert is right. Too long ago I tinkered with that, so I lost the details...

q.

[Modesto Vega]

Foreign keys, indexes, unique constraints, and triggers are modelled as operations. I don't see any column stereotypes involved in this. Their attributes are modelled as operation parameters with the columns names stored as parameters, column IDs are not referenced. The association's source and destination roles refer to the name of the operation.

This is Sparx Systems very convoluted way of bypassing the fact that only 1 attribute can be specified as the source and destination roles of an association. Actually, it is not possible to create a relationship joining a column to one of these operations. The problem with this is that it mandates the existence of physical database objects, specifically unique indexes or constraints and FKs, with the right columns in order to model any relationships.

Foreign keys, indexes, unique constraints are database objects in their own right. Hence, if I would have implemented them as elements and not as operations.

[qwerty]

You could setup your own profile for that purpose. I'd propbably do so if I had to go into these matters. You would probably not ba able to use the standard EA gimmicks built around DB modeling. Depends on whether that would make you cry.

q.

[Paolo F Cantoni]

You could set up your own profile for that purpose. I'd probably do so if I had to go into these matters. You would probably not be able to use the standard EA gimmicks built around DB modelling. Depends on whether that would make you cry.

q.

To echo q's points, if you wish to use EA's built-in functionality, you have to understand it is totally hardcoded to the various stereotypes.  That's why we decided that the "two-models" approach was the "least worst" option.


Paolo

[Modesto Vega]

You could set up your own profile for that purpose. I'd probably do so if I had to go into these matters. You would probably not be able to use the standard EA gimmicks built around DB modelling. Depends on whether that would make you cry.

q.

To echo q's points, if you wish to use EA's built-in functionality, you have to understand it is totally hardcoded to the various stereotypes.  That's why we decided that the "two-models" approach was the "least worst" option.


Paolo

As long as I can extend the EAUML::table stereotype by creating a weak association, I have no problem reusing the hardcoded stereotypes. Other than the naming and the fact that it does not support "weak" relationship - i.e., relationships that do not enforce referential integrity at database level - I don't actually have too much of a problem with the out-of-the-box stereotypes.