6 Replies Latest reply on Jul 4, 2019 7:14 PM by FC60

    ERD Best Practices




      Want to know the practices to create and maintain ERD.

      I have a database with 300+ tables

      I want to create one complete ERD and several small ERDs for every feature-module


      Can experts guide me the best ways to maintain ERD and how to keep all ERDs updated dynamically with on going structure changes in db.



        • 1. Re: ERD Best Practices

          That's a big question, and I don't think it can be answered in a few paragraphs here. But I'll give you a few tips to start you out.


          First, 300+ tables is pretty big to tackle all at once.  I suggest that you pick a subset of them - say one major table and its closest related tables, just to start. Data Modeler can reverse engineer from a data dictionary to a relational model.  While you can do it with an Import, if your database is an Oracle database, you can open a Data Modeler Browser from SQL Developer, open the Relational Diagram and drag tables from your database connection onto the diagram. Once you've saved your design, however, you will probably find it easier to do most of your work in the stand-alone version of Data Modeler.


          Now you've got a decision to make.  ERDs are in what Data Modeler calls the Logical Model.  Notice that when you first started reverse engineering, you engineered to the Relational Model.  A Relational Database diagram looks a lot like an ERD, and a lot of people are perfectly happy to call it an ERD, even though, technically, it isn't one.  If you're just trying to understand your database, and make minor changes to it - no major redesign, you may be happy to stick with the Relational Model.  One nice thing is that there is a way to capture database changes back to the Relational Model and changes in the Model back to the database. Personally, I don't like to do that, but you can.  It doesn't do that automatically - you have to manually tell it to update.


          But if you are doing lots of changes, you may want to reverse engineer your Relational Model to a Logical Model.  This gets away from the nitty gritty of tables, columns and foreign keys, and goes to entities, attributes, and relationships - a more concept-oriented style of design.  The Logical Model has true ERDs and is better suited to communicating your design to non-technical people.  However, it is now two steps away from your database, and it is more difficult to keep it in sync with database changes. On the other hand, my feeling is that once you commit to doing data modeling, all changes should go from model to database, never changing the database except through the model.


          Well that's my two cents - hope it is helpful.

          1 person found this helpful
          • 2. Re: ERD Best Practices


            another cent...

            A book: Oracle SQL Developer Data Modeler for Database Design Mastery

            Interesting to better understand the tool.

            1 person found this helpful
            • 3. Re: ERD Best Practices

              Thanks for such a detailed reply, its very help full.


              Need just one more detail, since Oracle Data Modeler is final choice, can you guide me how to achieve following manually at times:
              "One nice thing is that there is a way to capture database changes back to the Relational Model "

              • 4. Re: ERD Best Practices

                Read about this fonctions

                1 person found this helpful
                • 5. Re: ERD Best Practices