I would like to be able to create “logical” and “physical” designs (i.e. ERD’s) of my tables before writing the SQL to create tables and relationships.
This probably sounds patronising, but it really isn’t meant that way.
Have you tried the good old pencil + paper approach?
I find any kinds of software designed to help get a visual mental picture of something aren’t as good as pencil and paper. That might be just me, but its worth a shot!
Yes, I am a big fan of “pencil + paper”, HOWEVER, it is much easier to use a CASE tool because you can…
Store ERDs
Easily modify ERD’s
Relate ERD’s to your Data Dictionary
Convert “logical” ERD’s to “physical” ERD’s and relate each in a “library”
** Perform Table Generation from ERDs (i.e. “Forward Engineering”)
** Perform ERD Generation from Tables (i.e. “Reverse Engineering”)
and lots more…
Many years ago I used Popkin’s System Architect, but it cost thousands of $$$.
There are also some IBM tools, TOAD, etc, but I was hoping maybe there was a fully-functional, open-source ERD.
Maybe something from the MySQL or phpMyAdmin teams?!
So which tools on that page have you used and like?
(You certainly can’t be doing complex systems entirely on paper?!)
I have tried MySQL Workbench, but am not crazy that…
The Reverse Engineering didn’t insert relationship lines for me
That the relationship lines do not appear next to the relevant field. (You have to hover over the line which is in the middle of the table to figure out where it goes to?!)
I don’t mind experimenting, but it also helps to have someone who is more experienced point a newbie in the right direction and save me the struggle!
(You have a fancy badge by your name, so you must be important. Certainly a more “sophisticated” database person like yourself would feel CASE tools are an improvement over paper for serious projects?!)
I don’t think he even needs paper. Databases is what Rudy is proficient at, he breathes SQL and eats datatypes. To help forum members solve problems he doesn’t ask for a table diagram, but the MySQL code describing it.
Something makes me think he just knows what’s going on. I don’t work with advanced tables by any means, just the usual stuff. The most joins I’ve had in a single query is about 3, so the way I do things may not be the same as an expert who’s used to large database structures - however, my method is simple; Memory.
Well, I can’t think of any times where I needed to do more than 3 joins either. However, I find it helps me enormously when I can look at all of my tables and the joins between them. (I have had databases with 20-30 tables, and that is too unwieldy to memorize.)
And back to an earlier point…
I am working with someone else’s database design, and so for me, the quickest way to get up to speed is to see an ERD. (If I had created things from scratch, I could likely do things - more easily - in my head as well.)
Hoping someone out there has used an open-source tool, that is a stable project, that does ERD’s (including Reverse Engineering), and was happy with the tool.
(I hate to admit it, but MS Access’s “Relationship Window” does a great job considering how basic it is.)