Toad World® Forums

Duplicate columns created during reverse-engineering from database

I have two models, each representing different applications. They need to stay separate because one of the models is a shared application and to put all of my applications in the same model would be gigantic and I would think difficult to manage at times.

There are foreign keys between the two models. The foreign keys are manually maintained in a separate ddl file.

I want to create a merged model for sharing purposes only.

Model A - my applicatoin

Model B- the shared application

Model C- the newly created merged model

Script F- the foreign keys between the 2

I wanted to keep the color schemes and subject matter areas from both models, so I chose to create the merged model using the model merge functionality. I merged B into A and saved out to C. That seems to have been fine.

As to be expected, the foreign keys in script F will not be included in the merged model.

It seems that there are 2 choices:

  1. update the model from the database

For this option, select the tables from both schemas, which yields a list of tables and relationships on the merge screen; I then unselect everything and select only the relationships because that is all I need. The resultant merge creates duplicate column names in the tables instead of just adding the relationships. The relationships know their source and destination table/column(s), and those columns are already in the model. Why does it create duplicates? Is there something that I am doing wrong?

  1. update the model from the ddl

I tried to update the model from script F, which contains only alter table add constraint statements (no create table statements). The difference dialog never sees the constraints from the file. If it is even possible to do this, what are the correct options to use to make the constraints appear in the merge dialog?

And one last question…for situations where there are multiple model documents, what is the recommended way to maintain foreign keys between the two? In the future, there is going to be another application with hundreds of tables in the schema, and hundreds of foreign keys to the shared model ©. Maintaining all of the foreign keys manually is going to be a huge pain, and I’m hesitant to create a combined model that will eventually have thousands of tables in it.

Hello Mary,

ad 1) This is good way, but you need select more objects instead only relationships. You see on merge screen in Child Entity difference about columns. Problem is that column which created as propagated by relationship is not mapped to origin column in child entity (you can see it in attached picture), although they have same names (reason of this behavior is too much technical). So you will see, that one column is add and one is drop. Column to add is required to create relationships, so it will be always added when you check this relationship, but when you unselect all others changes, you will unselect dropping origin column.

ad 2) We cannot reverse eng. only relationships without child and parent entities. So it is reason why this workflow is not correct.

We know that workflow for your purpose is not clear, but foreign keys between models is complicate problem. I hope to better solution in future.

Best regards Daril