Maintain multiples DB's from the same model

Toad Data Modeler
1

Hi,

is it possible to maintain multiple databases from the same TM model file?

I have 4 different models of different services and 1 model that describe all of the info the 4 different services have in common.
Can i use Toad DM in the same way i use submodels to model different databases?

Thanks

2

It is possible. But only for generating new database.

When you generate DDL scripts, you have 3 choices:

  1. generate entities by GENERATE property
  2. Generate entire model.
  3. generate by workspace model.

But it’s not possible to compare model among different databases the way you would like to.
Becuase the model comparer compares whole models, workspace is only one object in the compare process. There is also referential integrity, inheritances, domains, etc. that are not workspace dependant.

For each compare process you would have to manually deselect all objects (workspaces, entities, domains, data types,…) from multi database model that are not in the single database model.

3

You say:
“There is also referential integrity, inheritances, domains, etc. that are not workspace dependant.”

So i can maintain different databases that use inherited tables (and modified) from a master database model? Also referential integrity will be maintained over different databases?

Can you explain me how to set this up?
Is this done from within 1 workspace or different workspaces?

Thanks!

4

In the first place, TDM3 does not support multiple databases.

What you can do is, import all databases into single model.

But there is problem when the databases contain same tables.
You can’t have multiple entities with the same name in one model.
And the inheritance is for extending entities in logical model.
Inheritance in physical model is possible for postgresql. Both inheritances
extend one table by adding additional attributes/fields. It is is not possible
to alter database structure.
If you want to synchronize altered tables, you have to have each database in separate model (reverse engineered) and compare these models with comparer tool (in TDM3).

Let’s say MASTER is the oriinal model from which you created copies.
“inherited” or “copy” database is the copy. It can contain additional fields, tables, changes.

What I would do is :

  1. reverse engineer all databases, each to single physical model (that actually happens when you reverse engineer).

  2. reverse engineer master database (if you do not have it as a model already) into separate model.

  3. compare this model to other models, importing additiional tables to master database, maybe also new attributes of tables in those inherited databases.

  4. recreate referential integrity (relationships) between old tables in MASTER database model and imported tables/fields from “inherited” database models.

  5. Now you have master database model with all the relevant changes in copies that should be, or are a ood addition to the master database.

  6. For each database copy create separate workspace on master database model, containing tables that exist or should exist there. If you plan to uprade all databases to the same liking, there is no need to do this.

  7. No you can compare the master database model to each “copy”
    and create specific alter script for each database.

Using separate databases with referential integrity would be good for me only in two situations: If you have multiple customers that from time to time do some alternations in their databases themself and you want to clean up the mess, or to separate tables in “categories” that exist in different databases. The mix of these two is not good, and my list will not help you in such a scenario (in TDM3).