importing disabled foreign keys from Oracle

[marcel700]

Hello,

if someone could help me, i have following problem. We have Oracle DB on project and i was given a task to create a physical model of the whole project. I can reverse engineer the database into EA model, but there are just few foreign keys defined physically in the database. Most of the table dependencies are not defined as foreign keys (due to performance reasons, as there are tables with more than hundred of columns and there would be tens of FKs on each table), but i want them in the model, to be in one place. But there's a problem how to get these dependencies into model. One way is to define them manually in model, but then i will loose the ability to manage the model and synchronize it with database, as it will not be 1:1 and the model would differ from the database. So i tried to map all the dependencies into one table and define them programmatically (out of this table) as disabled foreign keys on every table in the database, which seems to work somehow (except some details). I did this on database clone, just to try if the concept works, so i could use this clone as an intermediary to create and synchronize model (i'm able to make a fresh clone and define FK's back with script, if i need to synchronize). Then i was able to reverse engineer all the FKs (the original ones which are enabled and even the new ones that are disabled) into the model through import DB schema from ODBC in EA. The problem is, that I can't see in the model which FKs are enabled and which are disabled. Is there a way how to distinguish them in EA model? Or, maybe you could advise me better solution to my problem (how to denote "non-defined FKs" - dependencies into model).

I'm using Oracle 11.2 and Enterprise Architect 13.5

Thank you in advance for any suggestions.

[Geert Bellekens]

You could use a naming convention to see which ones are "real" and which ones aren't

Otherwise you might want to try and find another property you can set on the foreign keys that you don't use, but does get imported.

Geert

PS. I do like your solution to this problem. We did something similar, except that we used a little tool we wrote to import the "logical" FK's as dependencies.
The synchronize function simply ignores these dependencies, so they are not in the way.
And the dependencies show allow us to clearly see the difference between "real" FK's and "logical" FK's.

PPS. I still believe the best option would be to use real foreign keys on databases like this, and solve the performance issues a better way. Deleting FK's is really a very poor "solution" to your performance problem. Sacrifying data consistency for speed. It defeats one of the major benefits of relational databases; might as well go back to using flat files

[marcel700]

Hello Geert,

thank you for your suggestions. The naming convention could be good solution, but i'm still thinking also about the second one.. Is there a way how could i achieve this using built-in function in EA? i mean, using reverse engineering tool - import DB schema from ODBC. Is it possible to make some setting to this tool, so that i can influence how RE function works and maybe tell it to use some unused property to denote the FK is disabled? e.g. using tagged value on connector? Or I just need to write my own script to do that. If i understand it right, i should then use 2 steps. First step is using built-in RE, and the second is to use script over the imported data to denote it the way i will decide. But the script have to distinguish enabled/disabled FK too, so i still would have to use naming convention and than transform it to something else using script. Or did i misunderstand this?

PPS. I still believe the best option would be to use real foreign keys on databases like this, and solve the performance issues a better way. Deleting FK's is really a very poor "solution" to your performance problem. Sacrifying data consistency for speed. It defeats one of the major benefits of relational databases; might as well go back to using flat files

This is something i can't influence right now. That is the state in which i've got it. I'm not allowed to make any changes to the database... That was why i decided to use an on-purpose DB clone, where i can make changes, at least for the sake of the EA model.

Importing the "logical" FK's as dependencies could be also good solution, but I'm afraid, i'm not so good in EA so far, to write my own import tool.. at least, i would need some guide or help, how to manage that... Anyway, thank you for your help.

Marcel

[Geert Bellekens]

No, you can't influence the RE process itself.
I was thinking about (ab)using one of the properties that was imported anyway, and that you might not use.

But a naming convention is probably a better idea.

In case you are interested, the tool I was talking about is this one: https://github.com/GeertBellekens/Enterprise-Architect-Toolpack/tree/master/ERXImporter

It starts from an Erwin export file (.erx), imports that into a database, and then uses that information to try and create foreign keys in a database.
If that succeeds, it creates the dependencies in EA.

It's a bit of a hacked-together solution, but it works good enough for us right now.

Geert

[marcel700]

Thank you very much for your response and very helpful ideas:-)

[Modesto Vega]

We also face a similar challenge with a database almost devoid of physical foreign keys but with all the metadata needed to enforced "logical" foreign keys stored in tables. There is another recent thread in this forum where we had discussion about this.

Not having physical foreign keys and using tables to store the metadata used to enforced "logical" foreign keys, defined when the system was configured, is a very common design practice used by many ERP systems.

Our approach has gone through 3 phases:

• Create the "logical" foreign keys, either manually or by importing them, as "physical" foreign keys using a naming convention to distinguish them from physical foreign keys
• Tried to customised Sparx data modelling capabilities by adding a "Logical" foreign key relationship
• Use the 2 model approach (physical/logical or physical/physical), Paolo Cantoni has written about it in this forum several times

We quickly abandoned 1 and 2, and are going to use a variation of the 2 model approach, specifically a variation of the physical/logical approach. The variation involves customising Sparx EA because it cannot handle relationships between multiple attributes, we are going to add to our MDG an element to handle complex relationships involving multiple columns and operators other than "=". We are planning to import the relationships either by "hacking" the Sparx EA repository or preferably by writing a script that uses the API and loads a pre-prepared data set.

[Paolo F Cantoni]

We also have used a two-model approach.  They are two physical models.  The implementation Model (reverse Engineered from the live database) and the Design Model (initially duplicated from the Implementation model.  We don't change the Implementation Model (since it would be wiped out on the next update).  The design model is used to track changes to the Implementation Model.  We create all the additional information (such as FK etc.) in the Design Model.  That allows us to use all the required componentry in the correct manner.

As with Modesto, we came to the only (in our view) viable solution.

Paolo