4 Replies Latest reply: Aug 15, 2013 5:27 AM by HansJK RSS

    Advice/Recommendation please? How to best handle big models with various schemas?

    HansJK

      Hi

       

      Using DM 3.3.0.747.

       

      We have several bespoke applications, each using its own data model in separate schemas.  They do share various entities/tables which are made available in a shared schema.  There are references to these shared tables, etc. (e.g. foreign keys)

       

      We want to embark on a process to reverse engineer and then maintain these applications' schemas in Data Modeller.

       

      I would really like your views on how to best approach this.  The main concern is that the bigger the design (or more models/sub-views) the slower actions like saving and loading it becomes.  Once you also have it version controlled more time is added onto saving and synchronising with the sub-version repository and between team members.

       

      The thinking is to primarily have each application (and hence its own schema) in separate designs (projects), but include tables from the shared/core schema as needed.  Would this be a good/recommended approach?

       

      How does one address potential changes made (differences introduced) that may conflict between these separate designs?

      e.g. in physical model the schema becomes a user that has permissions, etc.  and that same user may be in multiple designs.  Each design is version controlled, but in the end only exists once in the database.  If each design is synch'ed with the database, DDL generated and executed, this can cause one design to add permissions, etc. that the second DDL from the second design removes or replaces with its own?

       

      I hope my question/scenario is clear enough.  If not, please revert to me for more clarification.

       

      Thank you & Regards

        • 1. Re: Advice/Recommendation please? How to best handle big models with various schemas?
          Philip Stoyanov-Oracle

          Hi Hans,

           

          I think it's better to use DM 4.0 and have everything in one design.

          1) You can import all schemas you need into one design (one relational model). Save that design - important to do it before using "remote objects" approach

          2) Use "Create new models based on schema names" wizard available in context menu for relational model in browser. Wizard will create new relational model for each schema in originating model and create there tables and views that

          belong to that schema. All objects in originating model become read-only "remote objects".

          If table (in new model) has a foreign key that refer table in another schema (now that table is moved to another new model) then "remote object" presentation is created for referred table.

          At the end you'll have one large model with read-only remote objects and set of relational models that keep the track of foreign keys between tables in different schemas in database.

          You can keep the originating model (the large one) in order to have global picture or delete it.

          Properties from physical model are also transferred to new models in DM 4.0.

          3) Check "Show 'Select relational models' dialog" in "Preferences>Data Modeler" - upon "open design"  that will bring up dialog allowing to select which model   to be loaded. The choice is persisted and

          applied next time you open design (no need to see that dialog again if no change in what to be loaded).

          4) For each model you can create "DDL generation" configuration (one or more) and exclude remote object from generation in DDL. Permissions should be maintained in model where table is editable and where DDL for that table will be generated.

           

          The thinking is to primarily have each application (and hence its own schema) in separate designs (projects), but include tables from the shared/core schema as needed.

          I won't recommend it. I know it's possible to have remote object in another design but you'll face following problems:

          1) You cannot use design level domains and definitions in data types model

          2) Remote object from another design are sensible to location of that remote design. You need to maintain the same directory structure everywhere designs (all related) are checked out and they need all to be checked out before dependent design to be open

          3) You need to add remote objects manually while wizard described above will do it for you in the scope of one design

           

          The main concern is that the bigger the design (or more models/sub-views) the slower actions like saving and loading it becomes.

          The number of models shouldn't be a problem with support of "Select relational models" dialog. Subviews will impact loading if only they are large and are visible when "save" is used.

          Total number of objects in model doesn't impact "save' operation - only modified objects are saved.

          Yes, there is overhead for versioned designs - one more reason to move to DM 4.0 - SVN client 1.7 is used there.

           

          Philip

          • 2. Re: Advice/Recommendation please? How to best handle big models with various schemas?
            HansJK

            Hi Philip

             

            Thank you for that detailed reply! 

             

            We are currently reluctant to go to the early adopter version.

             

            1. The functionality you described "Create new models based on schema names" does seem to be available in 3.3. 
              Could we apply your recommendation in 3.3 then?
            2. How would you go about adding a new schema to the recommended way of working?
            3. Does the fact that DM 4.0 uses sub-version 1.7 client mean we have to upgrade our sub-version repository?
              We have a fairly big repository both from a physical size perspective and the number of projects in it.  It is currently on version 1.6.
            4. About size of design.  Are you saying that it would be ok to have one design, containing many schemas?
              We currently have 19 schemas of significance in our database.
            5. Also, I now potentially have 19 schemas in the relational part of the design.  This will all result in one huge logical model, which is not what we want, since (other than the common/shared schema) each schema is distinct in its purpose and hence the entities that it represents.  How to handle that?
              I know there are sub-views to use, but there will still be a main logical model that will just be too big.

             

            I know these are a lot of questions, but your insights and recommendations are really appreciated.

             

            Thank you & Regards

            • 3. Re: Advice/Recommendation please? How to best handle big models with various schemas?
              Philip Stoyanov-Oracle

              The functionality you described "Create new models based on schema names" does seem to be available in 3.3. 

              Could we apply your recommendation in 3.3 then?

              It'll work but as I wrote it won't transfer objects from physical model. You can get them if you repeat import form database for each new model - merge will show no differences and tables won't be selected - you need to select tables in order get their physical properties merged.

              How would you go about adding a new schema to the recommended way of working?

              It should work if you keep the name of existing schemas and models.

              Does the fact that DM 4.0 uses sub-version 1.7 client mean we have to upgrade our sub-version repository?

              We have a fairly big repository both from a physical size perspective and the number of projects in it.  It is currently on version 1.6.

              No, 1.7 client will work with 1.6 servers. You cannot use the same working copy - you need to checkout designs with new version.

              About size of design.  Are you saying that it would be ok to have one design, containing many schemas?

              We currently have 19 schemas of significance in our database.

              It'll be easier to manage such design. I don't know how your tables are spread over those schemas but let say each one has 100 tables. That means one model with 1900 tables If you split it to 19 models and add for each model 50 referenced tables in other models then you'll get

              19 models with 150 tables each one. Well 150 tables is nothing to be scared of.

              Also, I now potentially have 19 schemas in the relational part of the design.  This will all result in one huge logical model, which is not what we want, since (other than the common/shared schema) each schema is distinct in its purpose and hence the entities that it represents.  How to handle that?

              I know there are sub-views to use, but there will still be a main logical model that will just be too big.

              Remote objects a supported for relational model only for now. We'll extend it later. And remote objects won't be processed correctly in engineering between logical and relational models - I hope will get that working for DM 4.0 production. So probably it's better for you to wait for DM 4 production if you want to create logical model out of those 19 relational models.

               

              Philip

              • 4. Re: Advice/Recommendation please? How to best handle big models with various schemas?
                HansJK

                Hi Philip

                 

                Thank you for your detail reply.

                 

                Just one thing more to highlight in this please (to perhaps add to your bug/future enhancement taks )

                Remote objects a supported for relational model only for now. We'll extend it later. And remote objects won't be processed correctly in engineering between logical and relational models - I hope will get that working for DM 4.0 production. So probably it's better for you to wait for DM 4 production if you want to create logical model out of those 19 relational models.

                 

                Please note that we would like to be able to reverse engineer into not one but a logical model for each of the distinct systems (which each live in its own schema) with the shared/common schema of course being present in most of them (as remote objects).

                 

                and on our way forward (until 4.0 Prod is available):

                We are forging ahead with DM 3.3 and creating the separate relational models (per schema by hand) and then reverse engineering from data dictionary into them individually, so that physical model will have what it needs for them.

                 

                Just a bit of a bother that when I generate DDL it includes DDL for the remote objects.  Could this not rather be switched off?

                 

                Thank you & Regards