RDBMS 'Pretty' Schema

[BigLug]

OK, I hate to admit it but there is something Microsoft makes that I quite like.

What I like is the tool that comes with MS Access that allows me to print out a great visual schema of a database. It shows each table with all its fields and then shows relationships between each of these tables via lines with many-to-one / one-to-one / many-to-many links on the ends. It also shows primary keys etc.

Luckily (?) I got retrenched mid-last year and am now able to define my own set of tools. I use PostgreSQL which is the most excellent RDBMS I've ever used. But, of course, it doesn't come with the visual schema tools.

Something written in perl would be good and I don't mind if the output just uses ASCII characters. If its not in perl its no matter. I don't care what OS: I use mac, doze and *nix.

Why do I want this? Because I'd like to be able to print out the schema to tack on the wall. Maybe I need to draw it up by hand? But I'm hoping there's something out there.

If you don't know what I mean take a look at this. (4768 bytes)

[htoug]

The 'nice' graphical tools become useless when you get to run on a real-world production database. The number of tables simply overwhelm them.

Eg. in our system we have 2199 tables with a total of 22698 columns.

What we have is a semiautomatic system, that allows us to extract information about the relevant tables and their interrelations from our datadictionary and have them drawn on our design tool (Rational Rose, but you can use almost anything that allows you to move the parts of the drawing around, add to it, display columns etc). Which tables are relevant depends on what you want to describe/document at the moment. You nearly always need to 'fiddle' with the automatic output to clarify it for your current needs.

We find that a datadictionary separate from the databases is extremely usefull. In the datadictionary you describe what you want your database(s) to look like, and then you have a tool to 'pat the databases into shape' without loosing data (which becomes important when you have approx 30GB of it).

The datadictionary can contain information that your current database system cannot support (at least not in the version released now), but which describes your intended use - eg. we had constrints in the dictionary years before our vendor supported them, and we have foreign keys dsescribed, that we definetely don't want implemented in the DB for performance reasons, but it is nice (and often necessary) to know that they are there - you could make a script that checks them at certain festive moments.

It also helps keeping development, test, release and production databases in step, and documenting your changes - you integrate your datadictionary with your code management system (CVS, Perforce or whatever), and suddenly you can remember what you did a year ago, and the wierd piece of code is much more understandable.

Once you have tried it, you can't understand how you lived without it for so long - just like your CMS!

[diotalevi]

The closest finished script to what you asked for is PostgreSQL AutoDoc. I've used it for a Green Party database over here PNG (300K with color), Dia (10K, editable). The key here is you'll have to move everything all around so it fits nice. Incidentally - if anyone here wants to comment on the linked diagram then you're very welcome.

---

Fun Fun Fun in the Fluffy Chair

[graff]

Is it just me, or is there something wrong with how the "orgpeoplemembership" box was drawn? Apart from that, it seems to work nicely -- it's awfully big, but then so is the schema... (I guess I would have preferred a different way to draw the lines and arrow symbols for the foreign-key dependencies -- having the lines look more like those in the OP's image, extending horizontally from each box where the field in question in printed -- but that's just a nit-pick.)

[PodMaster]

Oh ye of little CPAN-fu: SchemaView-Plus

MJD says you can't just make shit up and expect the computer to know what you mean, retardo! ** The Third rule of perl club is a statement of fact: pod is sexy.

[gjb]

There's GraphViz::DBI, although I've never seen results of it.

Hope this helps, -gjb-

[tstock]

Postgres comes with PgAccess, a graphical frontend to postres, but not as nice as it could be.

The meta-data part that graff mentions is all there and available through the DBI layer also.

Tiago

[graff]

Cool graphic, but I couldn't quite guess what all the symbolism was supposed to mean... (I'm sure it's easy, once you learn it.)

Does PostgreSQL work like Oracle, storing the table names, column names, data types, constraints (like "primary key", "unique", "not-null", etc), and "foreign key" (join-column) relations in it's own set of distinctly named tables? If so, then doing a suitable set of queries on these tables will give you the data you need to create such a chart, and if you're already using DBI and DBD::Postgres, it should be pretty quick work to cook it up.

[ravendarke]

If you are able to define your own toolset, you could always go for a commercial product. Computer Associates has a product called ERWin, and though it is a bit pricey, it is a great Data Modeling tool. Coming in just behind, and for considerably less money, is Embarcadero's ER/Studio. Both are excellent tools for schema generation, scripting, reverse engineering, and plain ol' printing. If you have a budget, you might want to give them a shot. If not, feel free to ignore me.

Or do what I do: Beg. :)

Marty

[flyfishin]

Check out Case Studio.

[khudgins]

makes a nice tool that's far less expensive than ER/Win or ER Studio. It's called Data Architect, and you can get it from them directly at http://www.thekompany.com