Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Sep 2011
    Posts
    7

    Unanswered: Need tool for drawing chart or diagram of complex existing database

    Hi
    I need to produce a poster with a chart or diagram of tables and relations in an existing database (90 tables, 1200 columns).

    Does anyone know of a tool that can pull this off and map the database in a sensible way more or less automatically? I would really prefer something that requires a minimal amount of manual work because it is so time consuming, the database evolves a lot, and I have to produce new versions now and then.

    And of course it has to be able to export it to a file for print (.pdf is fine)

    Thanks!
    /Rickard

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I use PowerDesigner for data modelling and documenting. You can use it to reverse engineer the diagram based on an existing database.

    You can use the generated diagram as-is, or you can edit to group related tables together to enhance the readability of the diagram. 90 tables is not a large data model.

    You can use it from there on to tweak your data model: first do it in PowerDesigner, then let it generate the code to alter your database. I always check the generated code before I let it run on a database.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Sep 2011
    Posts
    7

    Follow up

    Thanks! I downloaded a trial version of PowerDesigner and did a quick test. Well, not that quick perhaps, it took me a little while to get going and I am still not sure I am doing the right thing (but I think I am).

    I am now looking at something called Object Layout and it appears to be quite useless for my purposes. Less than 10% of the datamodel fits the screen, so I don't get an overview. I cant find a way to export or print. If I turn on "Show Referential Integrity" that just creates a 50% blue mesh backround that is impossible to follow.

    Am I doing something wrong?

    The hunt goes on...

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I don't have PowerDesigner (PD) at hand and reverse engineering a database is not a daily task.

    But I remember this: When reverse engineering a database, PD will ask if it can query the database on line (ODBC, ...) or based on a file with the DDL scripts (CREATE TABLE, VIEW, indexes, UDF, Sprocs, triggers, ...) of the database.

    PD will then create a Physical Data Model (.PDM) with all the objects it found. I guess your main interest are the tables and their relations. The tables will be ordered in one or another way (like alphabetically) that will most likely not present the tables in a way that is sensible to humans. That is something you will have to do yourself.

    You can do it in the main PDM model or
    you can create one or more (sub)models and extract the tables you want to see together from the main model and paste them in the model(s) you just created.

    Once the PDM is finished, PD can generate a Conceptual Data Model (.CDM). I prefer the CDM over the PDM, it is less cluttered that the PDM and is the relations between the tables are clearer.

    You will have to invest some time rearranging the tables and the relationship lines between them. But once that is done you have a nice overview of your database.

    I would use PD to redesign your database and use the code it generates (after proof reading it) at to change your database.

    The alternative: after each change in your database, reverse engineering it over and over again to a PDM (or CDM) is really not a valid alternative.

    I use PD, but there are other good data modelling tools available, like ErWin, Toad, ... Just download a few trial versions and see which one you like the most. I have used Rational Suite a number of years ago. I am not going to recommend that one. But YMMV.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I realize that this doesn't answer the original question as stated, but it may be a better answer in the long run. I'd suggest looking at SQL Spec. It will build a full blown data dictionary of many flavors of database, and then spit out a web site or a stand alone Windows Help File (CHM).

    It is one of the most used tools in my database arsenal and it is supported by a DBForums moderator (jezemine)!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Thanks Pat!

    SQLSpec will also generate ERD (with help from graphviz). here's an example:

    http://elsasoft.org/images/alltables...s_detailed.png

    EDIT: on the minimal work front, it's nice because you can drive it from the cmd line so it's easily automatable. You could have a nightly job that runs and generates new docs + ERD and pushes the result to an intranet so the docs are always up-to-date. You could even make it part of your continuous integration build process if you have one.
    Last edited by jezemine; 09-24-11 at 02:38.

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by jezemine View Post
    Thanks Pat!

    SQLSpec will also generate ERD (with help from graphviz). here's an example:

    http://elsasoft.org/images/alltables...s_detailed.png

    EDIT: on the minimal work front, it's nice because you can drive it from the cmd line so it's easily automatable. You could have a nightly job that runs and generates new docs + ERD and pushes the result to an intranet so the docs are always up-to-date. You could even make it part of your continuous integration build process if you have one.
    The example you gave, is that an ERD it generates out of an existing database? Without any manual editing?

    The example only has a fist-full of tables. Hos does it perform with hundreds of tables?

    I saw in "GUI page 3" a reference towards DTSX. We have a few hundred DTS packages in SQL Server 2000, some are old and haven't been changed or investigated for 8 years. Making an impact analysis is very hard. We want to rewrite the functionality in SQL Server 2008 R2. What help could SQLSpec offer us in regards to those DTS packages?

    Some CAD software for making integrated circuits, can rearrange the IC's and connection lines between them to minimise the number of crossing lines (to minimise the number of holes to drill through the board). I have long been thinking about something similar for reverse engineered ERD's. It would make the result a lot more readable. Is that what "graphviz" does?
    Last edited by Wim; 09-24-11 at 05:23.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  8. #8
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    graphviz is a diagram drawing package from AT&T Bell Labs, you can get it here: http://www.graphviz.org

    I use it for rendering diagrams because rendering diagrams is hard, and it's a solved problem. graphviz is pretty good for a free package. It does try to minimize the number of crossings and strives for a "pretty" placement of things. I did try it myself once and came up with something using simulated annealing that was ok, but very slow. graphviz is very fast.

    The example was generated from an existing db with no manual editing of the diagram.

    It does work with hundreds of tables but it's kind of a mess to look at because you have to scroll around. If you zoom out to see all of them, then you can't read anything. with sqlspec you can pick and choose what objects you want in the diagram (if you want) to make things a little more manageable for the eyes.

  9. #9
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    What kind of information can SQLSpec extract from a DTS package?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  10. #10
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    it only works against SSIS, not DTS. Here's a sample:

    Database Documentation

  11. #11
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by jezemine View Post
    it only works against SSIS, not DTS.
    Pity.
    Thank you for the information.

    RickardB, SQLSpec may be what you need.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  12. #12
    Join Date
    Sep 2011
    Posts
    7

    Follow up

    SqlSpec was a good start, but it did not use the best (for my purpose) GraphViz parameters, in fact the output was quite useless. I stole the GraphViz .dot file from SqlSpec and spent ALOT of time trying to make a better GraphViz graph. I came very close, but not close enough...

    I also came very very close using TOAD, good enough for internal purposes, but not good enough to print and publish to customers.

    Think I will have to look at some other tools next...

  13. #13
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I would really prefer something that requires a minimal amount of manual work because it is so time consuming, the database evolves a lot, and I have to produce new versions now and then.
    I think the best way to handle this is to start using a database modelling tool.
    - Do a reverse engineering of your existing database once.
    - Manually edit the tables and their relations. (Yes, it is time consuming, but you only have to do it one time)
    - Start using the database modelling tool and use it to alter the database instead of reverse engineering the database periodically.

    An automatic graphical tool like GraphViz can do a lot to make the result look a lot better than other applications deliver (like PowerDesigner). But in the end, the result will most likely never be how it would look like when you do it yourself.

    90 tables is still a small data model. The longer you postpone it, the larger it will become.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  14. #14
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113
    Nobody has mentioned it yet.. and there's probably a good reason why.

    I find the Database Diargram feature of SQL Server 2008 to be quite useful - and apart from anything else it's auto-updating in both directions - you can change the diagram to update your database or change your database and the diagram will update.

    I haven't tested how it will scale to 90 tables. We have 1200 tables in our system but I diagram in sections with my biggest - so far - being 26 tables.

  15. #15
    Join Date
    Sep 2011
    Posts
    71
    Hello to all people ,I just want to add a small hint below

    Power*Architect is the way to go. It's free, open source, and does a really great job helping you build your ERDs. Plus, it works on Windows, Linux, and OSX.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •